Insert Formula within Workday "num_days" string

Hey,

This may be super simple & this feature is available in excel, so I think i am just missing something in front of the "-1" for Smartsheet to pick up a formula. I am trying to calculate the day for a "test" shipment to arrive, based on 3 factors - 1. workday (with holidays), 2. Scheduled delivery date of devices, and # of transit days, depending on the state. For the "num_days" portion of the formula, I am trying to multiply it by "X # of ship days".

Here is my formula .... I don't have the holiday portion in yet, because I received "invalid data type." I tried changing the column properties to date and text. Any help would be great! Thanks!

=WORKDAY($[Device Delivery Date]1, -1 * [Mobile Device Ship Transit Days-Average]1)

Test info

Device Delivery Date: 8/26/20

Transit Time/Days: 1.2

Test Shipment date: Output

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

You need to make sure you are outputting a whole number, so you may need to incorporate a ROUND function.

=WORKDAY($[Device Delivery Date]1, ROUND(-1 * [Mobile Device Ship Transit Days-Average]1))

Or possibly a ROUNDUP or ROUNDDOWN or some similar function.

All comments

Paul Newcome

You need to make sure you are outputting a whole number, so you may need to incorporate a ROUND function.

=WORKDAY($[Device Delivery Date]1, ROUND(-1 * [Mobile Device Ship Transit Days-Average]1))

Or possibly a ROUNDUP or ROUNDDOWN or some similar function.

Julia Bennfors

Thank you Paul this worked!

Final output if anyone needs :)

=IFERROR(WORKDAY(WORKDAY($[Device Delivery Date]1, ROUNDUP($[Mobile Device Ship Transit Days-Average]1 * -1), {Mobile Ship/Holiday Key - Holiday Dates}), -3, {Mobile Ship/Holiday Key - Holiday Dates}), "TBD")

{Mobile Ship/Holiday Key - Holiday Dates} = My alternate sheet source to holidays

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.