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?!?!?!
