Use of WORKDAYS or NETWORKDAYS to return weekday date on a calculated date field

Options

I have two kinds of calculated date fields in columns that work, but I need them to only return weekdays/workdays.

Is it possible to use WORKDAYS or NETWORKDAYS on a calculated field to only return weekdays or workdays?

Here are the date field examples:

.....PlannedEnd2 equals PlannedStart2 plus 5 days......

     =[PlannedStart2]@row + 5

.....PlannedStart2 = One day after ActualEnd1 or PlannedEnd1 if ActualEnd 1 is blank.......

    =IF(ISBLANK([ActualEnd1]@row), [PlannedEnd1]@row + 1, [ActualEnd1]@row + 1)

So if the result of either of the above is a Saturday, how can I get the formula to return Monday's date? Thank you in advance.

Tags:

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @R12

    The WORKDAY() function should give you what you need. I swapped the order of your original formal to use ISDATE() instead of ISBLANK(). In the event text or other non-date response was added to the column, the cell would not be blank but would not be returning the data-type the WORKDAY function was expecting. The ISDATE looks for dates

    =IF(ISDATE([ActualEnd1]@row), WORKDAY([ActualEnd1]@row, 1), WORKDAY([PlannedEnd1]@row, 1))

    Does this get you what you need?

    Kelly


  • R12
    R12 ✭✭
    edited 05/05/22
    Options

    So far says #UNPARSEABLE. I will try modifying.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @R12

    If you're still having trouble with this formula, would you be able to post a screen capture, showing the formula open in the cell? (But please block out sensitive data).

    Thanks,

    Genevieve

  • R12
    R12 ✭✭
    Options

    From the answer above,

    =IF(ISDATE([ActualEnd1]@row), WORKDAY([ActualEnd1]@row, 1), WORKDAY([PlannedEnd1]@row, 1))

    I substituted actual field names into the formula (and it says #unparseable):

    .ActualEnd1........STEP 7B - TEAM LEAD SIGNOFF [DATE] *ACTUAL*

    .PlannedEnd1........Forecasted Finish Date1

    =IF(ISDATE([STEP 7B - TEAM LEAD SIGNOFF [DATE] *ACTUAL*]@row), WORKDAY([STEP 7B - TEAM LEAD SIGNOFF [DATE] *ACTUAL*]@row, 1), WORKDAY([Forecasted Finish Date1]@row, 1)

    Thank you.

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 05/09/22
    Options

    Hi @R12

    You're receiving an error because the title of your column includes [these], but in a formula, [this] indicates a column name.

    If you just copy/pasted in the name:

    [STEP 7B - TEAM LEAD SIGNOFF [DATE] *ACTUAL*]@row

    You'll get an error. Try typing out the formula by clicking on the cell you want to reference so it auto-populates the column name into the formula in a way it can read:@R12


    =IF(ISDATE([STEP 7B - TEAM LEAD SIGNOFF \[DATE\] *ACTUAL*]@row), WORKDAY([STEP 7B - TEAM LEAD SIGNOFF \[DATE\] *ACTUAL*]@row, 1), WORKDAY([Forecasted Finish Date1]@row, 1))


    The \ around [ will allow the formula to read your column name correctly.

    Cheers!

    Genevieve

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Cool. I didn't know that about embedded brackets in column names.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!