MrExcel Publishing
Your One Stop for Excel Tips & Solutions

date formula


Posted by david m on February 07, 2002 8:18 AM

Is there a formula that could be placed in multiple cells that would display different dates based on a date entered as criteria in a separate cell? For example, assume a 7 day work week ends on Saturday Feb. 9th. I key in 02/09/2002 in cell A1 - can I put a formula in cells A2:A7 that would automatically display the dates for the six workdays (Sunday thru Friday) leading up to 02/09/2002?


Posted by Todd on February 07, 2002 8:22 AM

In cell A2 do =A1-6
In cell A3 do =A1-5
and so on. If you had just a 5 day work week,you would put
In cell A2, =Workday(A1,-6)

Posted by Steve Hartman on February 07, 2002 8:28 AM

Another way , less typing

In A2 put =A1-6. In A3 put =A2+1. Copy A3 and Paste to A4:A7.

Is there a formula that could be placed in multiple cells that would display different dates based on a date entered as criteria in a separate cell? For example, assume a 7 day work week ends on Saturday Feb. 9th. I key in 02/09/2002 in cell A1 - can I put a formula in cells A2:A7 that would automatically display the dates for the six workdays (Sunday thru Friday) leading up to 02/09/2002?

Posted by Mark W. on February 07, 2002 8:35 AM

Such a formula is known as an array formula...

Select cells A2:A7, type =$A$1-{1;2;3;4;5;6},
press Control+Shift+Enter, and format A2:A7
as a date. A properly entered formula will
appear as {=$A$1-{1;2;3;4;5;6}} in the formula
bar.

Posted by david m on February 07, 2002 1:36 PM

Thanks!

Thanks so much... I was banging my head against a wall and it was so simple! I just couldn't pick that out of the Excel Help contents for some reason. Isn't this site great?!?!?!