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

R12 · 05/04/22

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.

Kelly Moore · 05/04/22

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

https://help.smartsheet.com/function/workday

https://help.smartsheet.com/function/isdate

R12 · 05/05/22

So far says #UNPARSEABLE. I will try modifying.

Genevieve P. · 05/05/22

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 · 05/06/22

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. · 05/06/22

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

Screen Shot 2022-05-06 at 4.16.34 PM.png

=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 · 05/06/22

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

Genevieve P. · 05/09/22

https://community.smartsheet.com/discussion/comment/328699#Comment_328699

Me either, til I tested 😂. Learn something new every day!

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

Answers

Help Article Resources

Categories