Saturday, Sunday or Holiday BY-PASS

**GPErtel** · 11-15-2013, 03:35 PM

I have a patient visitation spreadsheet. Normally, when a patient is seen a 30 day followup will be posted.
I want to post a visit date and the next cell will display the scheduled 30 day date. No check for Saturday, Sunday or Holidays.
I have all holidays listed in another worksheet.
My current formula is: =IF(NOT(ISERROR(DATEVALUE(TEXT(D4,"mm/dd/yyyy")))),D4+30,"")
What I what is to select the very next date after Saturday, Sunday or Holiday.
Any assistance work be greatly appreciated.
Thank you,
Glenn

**amit.wilson** · 11-16-2013, 06:22 AM

Hi GPErtel,

welcome to the forum.

If the date is entered as mm/dd/yyy in D4, then you could try this:

Formula:

=IF(ISBLANK(D4),"",(D4+30+IF(OR(WEEKDAY(D4+30,2)=7,(--ISNUMBER(MATCH(D4+30,Holidays,0)))=1),1,IF(WEEKDAY(D4+30,2)=6,2,0))))

This needs you to name range of cells with the holidays as 'Holidays".

The current formula seems complicated. Are you trying to avoid blanks or errors? There may be a shorter formula to do that. If the date is entered as mm/dd/yyy then most of your current formula is not required.

Let me know if it works or needs to be tweaked.

Cheers

**daddylonglegs** · 11-16-2013, 07:55 AM

You can use WORKDAY function to get the next working day, so to add 30 days.....but jump forward to the next workday if that's a non working day

=IF(COUNT(D4),WORKDAY(D4+30-1,1,Holidays),"")

**GPErtel** · 11-19-2013, 06:43 PM

First, I thank those of you that have responded.
Now, you know when you get close to putting the fire out, there is almost always an amber that wants to flame up.

Well, the ambers were stirred and I received a completely different set of instructions.
Here is what I (we) need...
Patient next appointment date 30 days out, cannot exceed 30 days...
If this date falls on a Saturday or Sunday or Holiday the appointment will be schedule the prior workday.
Good example was given: 11/28/2013. Holiday 1s the 28th (Thu) and 29th (Fri) both on the holiday list also 30th (Sat) and 31st (Sun) weekend days.
So if the 30 day appointment falls on Sunday it needs to fall back to the previous Friday, then check the Holiday list if it is a holiday minus one and recheck holiday list, etc...
Saturday will work the same.
This is much more than a simple equation... I am baffled...
Thank you in advance, I hope with support I can come up with a solution.
Glenn

**daddylonglegs** · 11-19-2013, 07:06 PM

You can easily adjust my suggestion - my original version gives you the 30 day appointment but goes forward to the next working day if that isn't one. To go back to the previous workday use this version

=IF(COUNT(D4),WORKDAY(D4+30+1,-1,Holidays),"")