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

R12

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.

Find more posts tagged with

Formulas

Accepted answers

All comments

Kelly Moore

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

So far says #UNPARSEABLE. I will try modifying.

Genevieve P.

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

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.

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

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

Genevieve P.

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.