Excel Dates

[Dave Porter]

I have a column of dates. In the next column I want to automate cells with (for example) =O34+21 to show a date 21 days after the date in cell O34. However, I do not want weekends included. I would also like to not land on a holiday if possible. I am pretty much a novice with formulas. Any help would be appreciated.

 

[JoeMo]

Take a look at the WORKDAY function in Excel Help.

 

[Norie]

Try this.

=WORKDAY(O34,21)

 

[Dave Porter]

Try this.

=WORKDAY(O34,21)

Interesting. When I use the =O36+21 I get 12/23/16. Yet when I use the =WORKDAY(O36,21) I get 1/2/17. O36 is 12/2/16 and is manually added to this column (O). Am I doing something wrong?



Bàthaidh toll beag long mhòr

'A little hole will sink a big ship'

 

[JoeMo]

Interesting. When I use the =O36+21 I get 12/23/16. Yet when I use the =WORKDAY(O36,21) I get 1/2/17. O36 is 12/2/16 and is manually added to this column (O). Am I doing something wrong?



Bàthaidh toll beag long mhòr

'A little hole will sink a big ship'

Takes longer to get to 21 workdays out b/c you are excluding weekends.

 

[Dave Porter]

Takes longer to get to 21 workdays out b/c you are excluding weekends.

Oh, Ok. Then this may not be the formula that I need. I just don't want it to land on a weekend, but rather the first workday after a weekend. Is there another formula for that perhaps?

 

[JoeMo]

Oh, Ok. Then this may not be the formula that I need. I just don't want it to land on a weekend, but rather the first workday after a weekend. Is there another formula for that perhaps?

Maybe:

=IF(WEEKDAY(O34+21)=7,O34+23,IF(WEEKDAY(O34+21)=1,O34+22,O34+21))

 

[Tetra201]

... I just don't want it to land on a weekend, but rather the first workday after a weekend. Is there another formula for that perhaps?

Is this what you need?

=O34+21+MAX(0,3-WEEKDAY(O34+21,16))

 

[Dave Porter]

Is this what you need?

=O34+21+MAX(0,3-WEEKDAY(O34+21,16))

I think that this is just what I need. Thank you so much!

 

[FormR]

Hi, here is another option:

=WORKDAY(O34+20,1)

And to also skip holidays: (where holidays refers to a range of cells that list said holiday dates).

=WORKDAY(O34+20,1,holidays)