Change Date based on Symbol

Good day everyone,

I've seen a lot of posts about changing a symbol based on the date, but none for the other way around. In my use case, I have a Priority symbol that is being filled in on a form. I need to create a due date based on the Priority chosen and the date the form was entered.

I was able to build that initial formula with no problem.

=IF(Priority@row = "High", [Date of Input]@row, IF(Priority@row = "Medium", [Date of Input]@row + 3, IF(Priority@row = "Low", [Date of Input]@row + 5)))

Where I am getting tripped up is adding in the WORKDAY function as I only want the due date to be on work days. This includes if a "High" priority request happens to be entered on a weekend. I tried the following but am getting an INCORRECT ARGUMENT error.

=IF(Priority@row = "High", WORKDAY([Date of Input]@row), IF(Priority@row = "Medium", WORKDAY([Date of Input]@row + 3), IF(Priority@row = "Low", WORKDAY([Date of Input]@row + 5))))

Thank you in advance for any help!

~Jaime

Find more posts tagged with

Sheets

Formulas

Accepted answers

Leibel S

See below, the WORKDAY function requires a date and how many days you want to add.

for the high priority it is a bit tricky (because you are not adding days). What you can do is as i show below, use the input day -1 then add 1 day. Let me know if this works

=IF(Priority@row = "High", WORKDAY([Date of Input]@row - 1, 1), IF(Priority@row = "Medium", WORKDAY([Date of Input]@row, 3), IF(Priority@row = "Low", WORKDAY([Date of Input]@row, 5))))

All comments

Nick Korna

Hi @Jaime Ciabattoni,

Combine with the WEEKDAY function:

=IF(AND(OR(WEEKDAY([Date of Input]@row) = 1, WEEKDAY([Date of Input]@row) = 7), Priority@row = "High"), WORKDAY([Date of Input]@row, 1), IF(AND(OR(WEEKDAY([Date of Input]@row) = 1, WEEKDAY([Date of Input]@row) = 7), Priority@row = "Medium"), WORKDAY([Date of Input]@row, 3), IF(AND(OR(WEEKDAY([Date of Input]@row) = 1, WEEKDAY([Date of Input]@row) = 7), Priority@row = "Low"), WORKDAY([Date of Input]@row, 5), IF(Priority@row = "High", WORKDAY([Date of Input]@row, 0), IF(Priority@row = "Medium", WORKDAY([Date of Input]@row, 3), IF(Priority@row = "Low", WORKDAY([Date of Input]@row, 5)))))))

Sample (using current month, formula above is the green column):

Hope this helps, but if I've misunderstood something or you've any problems/questions then just post!

Leibel S

See below, the WORKDAY function requires a date and how many days you want to add.

for the high priority it is a bit tricky (because you are not adding days). What you can do is as i show below, use the input day -1 then add 1 day. Let me know if this works

=IF(Priority@row = "High", WORKDAY([Date of Input]@row - 1, 1), IF(Priority@row = "Medium", WORKDAY([Date of Input]@row, 3), IF(Priority@row = "Low", WORKDAY([Date of Input]@row, 5))))

Jaime Elizabeth

@Nick Korna your formula does work, but I like the simplicity of the one provided by @Leibel S.

Thank you both!!!

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.