Skip to main content
Solved

WORKDAY() Formula not adding correctly

  • November 28, 2020
  • 5 replies
  • 47 views
L
Forum|alt.badge.img+5

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

Best answer by Lindsey_Bavaro

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

    5 replies

    L
    Forum|alt.badge.img+5
    • Lindsey_Bavaro
    • Author
    • Inspiring
    • 8 replies
    • November 28, 2020

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

    L
    Forum|alt.badge.img+5
    • Lindsey_Bavaro
    • Author
    • Inspiring
    • 8 replies
    • November 28, 2020

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

    ))

    L
    Forum|alt.badge.img+5
    • Lindsey_Bavaro
    • Author
    • Inspiring
    • 8 replies
    • November 29, 2020

    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, [holidays]). Don’t like that.

    L
    Forum|alt.badge.img+5
    • Lindsey_Bavaro
    • Author
    • Inspiring
    • 8 replies
    • Answer
    • November 29, 2020

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

    V
    Forum|alt.badge.img+3
    • vgerask
    • New Participant
    • 2 replies
    • June 24, 2024

    But isn't it a bug? 


    Terms & ConditionsAccessibility statement