WORKDAY() Formula not adding correctly

Ask the community Dates and timezones
#1

I need to ensure no dates in my base land on a weekend or holiday. I’m trying to use the WORKDAY(startDate, numDays, [holidays]) formula to accomplish this. However… issues. I’m using “0” as numDays since I do not want to add any days if the date already lands on a working day. This does not move the date however if it lands on a weekend or holiday. If I change numDays to “1”, then the date automatically moves forward a day, regardless if the original date already lands on a weekday. How can I resolve this? Is there a “if weekday” formula that I can add in?

See More

#2

Here’s the actual formula I’m using:

IF({Feasibility Firm Date (F25)},
{Feasibility Firm Date (F25)},
IF({Feasibility # Days (F25)}=BLANK(), “”,
IF({Feasibility # Days (F25)}>5,
WORKDAY(DATEADD({M/A (Entered In Contract Review Form)},{Feasibility # Days (F25)}, ‘day’),0,‘2020-01-01, 2020-01-20, 2020-02-17, 2020-05-25, 2020-07-04, 2020-09-07, 2020-11-11, 2020-11-26, 2020-11-27, 2020-12-25, 2021-01-01, 2021-01-18, 2021-02-15, 2021-05-31, 2021-07-04, 2021-09-06, 2021-11-11, 2021-11-25, 2021-11-26, 2021-12-25’),
IF({Feasibility # Days (F25)}<=5,
WORKDAY({M/A (Entered In Contract Review Form)},{Feasibility # Days (F25)}, ‘2020-01-01, 2020-01-20, 2020-02-17, 2020-05-25, 2020-07-04, 2020-09-07, 2020-11-11, 2020-11-26, 2020-11-27, 2020-12-25, 2021-01-01, 2021-01-18, 2021-02-15, 2021-05-31, 2021-07-04, 2021-09-06, 2021-11-11, 2021-11-25, 2021-11-26, 2021-12-25’
)))))

Do I need to add in a IF statement using DATETIME_FORMAT({Date Field}, ‘dddd’) and If the date lands on a Saturday and Sunday ONLY THEN do the Workday()?

#3

Playing around with the below but can’t seem to make it would. Formula is showing as invalid.

IF(

OR(

DATETIME_FORMAT(DATEADD( {M/A (Entered In Contract Review Form)},{Feasibility # Days (F25)}, ‘day’ ), ‘dddd’) = “Saturday”,

DATETIME_FORMAT(DATEADD( {M/A (Entered In Contract Review Form)},{Feasibility # Days (F25)}, ‘day’ ), ‘dddd’) = “Sunday”),

WORKDAY(DATEADD({M/A (Entered In Contract Review Form)},{Feasibility # Days (F25)}, ‘day’),1,‘2020-01-01, 2020-01-20, 2020-02-17, 2020-05-25, 2020-07-04, 2020-09-07, 2020-11-11, 2020-11-26, 2020-11-27, 2020-12-25, 2021-01-01, 2021-01-18, 2021-02-15, 2021-05-31, 2021-07-04, 2021-09-06, 2021-11-11, 2021-11-25, 2021-11-26, 2021-12-25’),

DATEADD({M/A (Entered In Contract Review Form)},{Feasibility # Days (F25)}, ‘day’ )

))

#4

My back up plan, which I hate, is to create another field, {Date}-1, so that WORKDAY({Date}-1, 1, [holidays]). Don’t like that.

#5

Ah-hah! Answered my own question. I can add the “-1” right into that formula instead of using another field:

IF({Feasibility Firm Date (F25)}, {Feasibility Firm Date (F25)}, IF({Feasibility # Days (F25)}=BLANK(), "", IF({Feasibility # Days (F25)}>5, WORKDAY( DATEADD({M/A (Entered In Contract Review Form)},{Feasibility # Days (F25)}-1, 'day'),1,'2020-01-01, 2020-01-20, 2020-02-17, 2020-05-25, 2020-07-04, 2020-09-07, 2020-11-11, 2020-11-26, 2020-11-27, 2020-12-25, 2021-01-01, 2021-01-18, 2021-02-15, 2021-05-31, 2021-07-04, 2021-09-06, 2021-11-11, 2021-11-25, 2021-11-26, 2021-12-25'), IF({Feasibility # Days (F25)}<=5, WORKDAY({M/A (Entered In Contract Review Form)},{Feasibility # Days (F25)}, '2020-01-01, 2020-01-20, 2020-02-17, 2020-05-25, 2020-07-04, 2020-09-07, 2020-11-11, 2020-11-26, 2020-11-27, 2020-12-25, 2021-01-01, 2021-01-18, 2021-02-15, 2021-05-31, 2021-07-04, 2021-09-06, 2021-11-11, 2021-11-25, 2021-11-26, 2021-12-25' )))))

Works perfect after doing a bunch of tests. YAY!!!

1 Like
#6

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.