Date function arguments

**KMTrombley** · 02-24-2010, 05:44 PM

Does anyone know how to setup a function argument for a start and end date that will allow for the calculation of the day of the week.

For example:
Start Date = Thursday, April 01, 2010 End Date = (StartDate+30) Saturday, May 01, 2010

I need the formula to return the value of a WEEKDAY date if the end date falls on a weekend date. I need it to subtract 1 day if the end date is on a Saturday or subtract 2 days if the end date is on a Sunday.

**Leith Ross** · 02-24-2010, 08:02 PM

Hello KMTrombley,

This formula assumes the date to check is in cell A1 and the first day of week starts on Sunday.
=IF(AND(WEEKDAY(A1)>=2,WEEKDAY(A1)<=6), A1, IF(WEEKDAY(A1) = 7,A1-1,A1-2))

**shg** · 02-24-2010, 08:20 PM

Another way, if A1 contains the start date, and this calculates the end date:

=A1 + 30 + LOOKUP(WEEKDAY(A1 + 30), {1,2,7}, {-2,0,-1})

**DonkeyOte** · 02-25-2010, 04:46 AM

Another way

=WORKDAY(A1+31,-1)

**KMTrombley** · 03-01-2010, 10:57 AM

Thank you so much for your help.

**KMTrombley** · 03-01-2010, 02:17 PM

If I use the following formula will it exclude Federal Holidays?

=WORKDAY(A1+31,1)

I just discovered that the workdays to be calculated need to exclude weekends and holidays, so if the end date falls on a Satuday it needs to be pushed to Monday; unless Monday is a holiday then it would need to be pushed to Tuesday.

**DonkeyOte** · 03-01-2010, 02:48 PM

You would add the holiday dates via the optional third parameter, eg:

=WORKDAY(A1+31,-1,Z1:Z10)

where Z1:Z10 is range containing public holiday dates - adapt as required.

(see XL Help files for more info.)