Adding days in Excel Office 365

**denisezuv** · 10-25-2021, 04:07 PM

Good afternoon,

I have a formula that takes a date and adds 5 days to it excluding weekends and Holidays

Easy yes

But I need it to only add an additional day IF the actual date falls on the holiday.

What is happening is

=WORKDAY(C8,5,V5:V24)

I have the date of 11/5/2021 - in C8
+ 5 Days
Exclude holidays that are located in V5:V24
This is putting my new date as 11/15/2021

Veterans day falls on 11/11/2021 so I see why it is adding the 6th day

BUT if the holiday does not fall on the "New" date then I do not want it to add the 6th day. I only want it to add the 6th day if the "New" date actually falls on a holiday.

Any help would be appreciated

Thanks

**6StringJazzer** · 10-25-2021, 10:53 PM

So in your example, you want the result to be 11/12.

If you started on 11/4, you would also want it to be 11/12.

So you can't use the holiday list in WORKDAYS. Use this formula to add a day if the calculated day falls on a holiday:

=WORKDAY(C8,5) + IF(ISNUMBER(MATCH(WORKDAY(C8,5),V5:V24,0)),1,0)

**bebo021999** · 10-26-2021, 12:36 AM

Originally Posted by 6StringJazzer

=WORKDAY(C8,5) + IF(ISNUMBER(MATCH(WORKDAY(C8,5),V5:V24,0)),1,0)

Your final result might fall into weekend.
For example: C8=5-Nov; Holiday=12-Nov ==> result=13-Nov (Sat)

Try to start from WORKDAY(C8,5) as starting date, back 1 day, then plus 1 working day:

=WORKDAY(WORKDAY(C8,5)-1,1,V5:V24) = 15-Nov

**6StringJazzer** · 10-26-2021, 08:13 AM

Originally Posted by bebo021999

Your final result might fall into weekend.

Excellent solution. I failed to consider all cases.

**denisezuv** · 10-26-2021, 08:45 AM

Thank you so much! This did exactly what I needed it to do!!!!!!!

Adding days in Excel Office 365

LinkBack

Thread Tools

Rate This Thread

Display

Adding days in Excel Office 365

Re: Adding days in Excel Office 365

Re: Adding days in Excel Office 365

Re: Adding days in Excel Office 365

Re: Adding days in Excel Office 365

Thread Information

Users Browsing this Thread

Similar Threads

Adding days and half days in an Attendance sheet

[SOLVED] Adding the correct number of working days to the chart, skipping off days

Office 2016 with Book: Special Edition Using Microsoft(R) Office Excel 2007

[SOLVED] Formula Help: Determine days total from arrival to current date then stop adding days

[SOLVED] Adding Total Number of Days Elapsed and Displaying >31 Days

Adding dates in Excel - Tracking fractional days

Adding Excel charts to PowerPoint presentation in Office 2003

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions