# Excel Dates

#### Dave Porter

##### New Member
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.

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Take a look at the WORKDAY function in Excel Help.

Try this.

=WORKDAY(O34,21)

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'

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.

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?

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:

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

... 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))

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!

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)

Last edited:

Replies
5
Views
38
Replies
3
Views
194
Replies
3
Views
105
Replies
5
Views
331
Replies
1
Views
264

1,202,984
Messages
6,052,912
Members
444,612
Latest member
FajnaAli

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back