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

WORKDAY() Formula not adding correctly

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()?

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()?

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

))

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

))

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

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!!!

But isn't it a bug?

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded