Excel formula date calculation help?

Question

I am working in 2016 Excel and trying to create a dynamic formula. I am trying to calculate how many days an item is on sale within each week it is on sale. For example, if an item is on promotion beginning 8/10 to 9/5, how many days is that item on sale within each week (week ending on Sunday)? I'm currently using a formula that subtracts the week ending date by the starting date +1 so that it is inclusive of the start date and end date but doesn't work for the remaining dates. Help?? Thanks!!!

I would be expecting B5=7, B6=3, B7=11, B8=4, etc.

Answer 1

Hello

I’m Adeyemi and I’d be happy to help you with your question.

You can use the following formula to calculate the number of days an item is on sale within each week it is on sale:

=IF(AND(C5<=$B$2,D5>=$B$3),MIN(D5,$A5)-MAX(C5,$A5)+1,0)

This formula checks if the start date (C5) is less than or equal to the end date of the promotion ($B$2) and if the end date (D5) is greater than or equal to the start date of the promotion ($B$3). If both conditions are true, it calculates the minimum of the end date and the week ending date ($A5) minus the maximum of the start date and the start date of the promotion ($A5), and adds 1 to include both the start and end dates. If either condition is false, it returns 0.

You can then drag this formula down to fill in the remaining cells in column B. This should give you the expected results: B5=7, B6=3, B7=11, B8=4, etc.

I hope this helps!

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below

Regards Adeyemi

Answer 2

Hi Adeyemi,

Thanks so much for responding--

Your formula only prompted back 0's when trying it out. Ideally, this formula would replace my formula already located in cell B4. I don't know why it would reference the header and a blank cell (B2 and B3) in the formula?

**Update: When updating the formula per your explanation, it now prompts 1's and a negative 3 (instead of a positive 3 for cell B6) Please see below.

Answer 3

I apologize for any confusion in my previous response. Here is a corrected formula that I edited

=MAX(0,MIN($A5,D$2)-MAX(C5,$B$3)+1)

This formula calculates the number of days an item is on sale within each week it is on sale. It checks if the start date (C5) is less than or equal to the week ending date ($A5) and if the end date (D$2) is greater than or equal to the start date of the promotion ($B$3). If both conditions are true, it calculates the minimum of the week ending date and the end date minus the maximum of the start date and the start date of the promotion, and adds 1 to include both the start and end dates. If either condition is false, it returns 0.

You can enter this formula into cell B5 and then drag it down to fill in the remaining cells in column B. This should give you the expected results: B5=7, B6=3, B7=11, B8=4, etc.

I hope this helps!

Regards Adeyemi

Answer 4

Is there reason why, or if, the formula should include the header? I'm following along with your description and the formula and but it references B3 as the start date thought it is the header?

I adjusted it per the description and it worked for the first and last two dates but having a bit of trouble with the middle three and they're resulting in higher numbers than expected (11, 14, and 11) as each should be <=7 due to it referencing days in one week at a time

Answer 5

In that case, try this

=MAX(0,MIN($A5,D$2)-MAX(C5,$A5)+1)

This formula calculates the number of days an item is on sale within each week it is on sale. It checks if the start date (C5) is less than or equal to the week ending date ($A5) and if the end date (D$2) is greater than or equal to the start date of the promotion ($A5). If both conditions are true, it calculates the minimum of the week ending date and the end date minus the maximum of the start date and the start date of the promotion, and adds 1 to include both the start and end dates. If either condition is false, it returns 0.

You can enter this formula into cell B5 and then drag it down to fill in the remaining cells in column B. This should give you the expected results: B5=7, B6=3, B7=7, B8=4, etc.

I hope this helps!

Regards Adeyemi