Is there an alternative for WORKDAY.INTL in Smartsheets?

Paul Newcome

What columns are you showing in your screenshots?

Liam Burnett

Im showing the Gateway 4 column which is being referenced on the left and the stage 1/2 submission which is the date being produced on the right and the 31-JUL-2020 date is the award date.

Paul Newcome

So if Gateway 4 is showing 16 June and "the stage 1/2 submission which is the date being produced on the right" is 16 June, then I don't understand what the issue is.

The Gateway 4 date is a Tuesday and the formula is leaving it as a Tuesday.

Liam Burnett

Hi

I cant see why this isn't working, if its a stage 2 then it does the workday formula and then if its a certain day of the week then it add the correct amount of days to it to make it a Tuesday, Wednesday or a Thursday. however it says its #UNPARSEABLE.

=IF([Award Date]5 = "", "award date reqd", IF([Activity Name]5 = "Stage 1", WORKDAY([Award Date]5, {Stg 1 Submission}, {Holidays Range 1}), IF([Gateway 4 (Date Format) - Hidden]5 = "", "GW4 date reqd", IF([Activity Name]5 = "Stage 2", WORKDAY([Gateway 4 (Date Format) - Hidden]5, -25, {Holidays Range 1}) + IF(WEEKDAY([Gateway 4 (Date Format) - Hidden]5) = 1, 2, IF(WEEKDAY([Gateway 4 (Date Format) - Hidden]5) = 2, 1, IF(WEEKDAY([Gateway 4 (Date Format) - Hidden]5) = 6, 4, IF(WEEKDAY([Gateway 4 (Date Format) - Hidden]5) = 7, 3)))))))))

Paul Newcome

You had 2 too many closing parenthesis on the end.

=IF([Award Date]5 = "", "award date reqd", IF([Activity Name]5 = "Stage 1", WORKDAY([Award Date]5, {Stg 1 Submission}, {Holidays Range 1}), IF([Gateway 4 (Date Format) - Hidden]5 = "", "GW4 date reqd", IF([Activity Name]5 = "Stage 2", WORKDAY([Gateway 4 (Date Format) - Hidden]5, -25, {Holidays Range 1}) + IF(WEEKDAY([Gateway 4 (Date Format) - Hidden]5) = 1, 2, IF(WEEKDAY([Gateway 4 (Date Format) - Hidden]5) = 2, 1, IF(WEEKDAY([Gateway 4 (Date Format) - Hidden]5) = 6, 4, IF(WEEKDAY([Gateway 4 (Date Format) - Hidden]5) = 7, 3)))))))

Liam Burnett

Thank you it stopped being #UNPARSEABLE but it doesn't seem to be adding the days on when its not a Tuesday, Wednesday or a Thursday. I have a date that it a Friday 05/06/20 and it should go to the next Tuesday 09/06/20 but doesn't am im not sure why.

=IF([Award Date]5 = "", "award date reqd", IF([Activity Name]5 = "Stage 1", WORKDAY([Award Date]5, {Stg 1 Submission}, {Holidays Range 1}), IF([Gateway 4 (Date Format) - Hidden]5 = "", "GW4 date reqd", IF([Activity Name]5 = "Stage 2", WORKDAY([Gateway 4 (Date Format) - Hidden]5, 5, {Holidays Range 1}) + IF(WEEKDAY([Gateway 4 (Date Format) - Hidden]5) = 1, 2, IF(WEEKDAY([Gateway 4 (Date Format) - Hidden]5) = 2, 1, IF(WEEKDAY([Gateway 4 (Date Format) - Hidden]5) = 6, 4, IF(WEEKDAY([Gateway 4 (Date Format) - Hidden]5) = 7, 3))))))))

Paul Newcome

That is because in your nested IFs your WEEKDAY function is looking at the weekday of [Gateway 4 (Date Format) - Hidden]5. Not the result of WORKDAY([Gateway 4 (Date Format) - Hidden]5, 5, {Holidays Range 1}).

You aren't comparing the same date. You need to drop the WORKDAY function into each of your WEEKDAY functions.

Liam Burnett

Ah that seems to have helped!

Thanks for all your help on this 🙂 It's much appreciated.

Thanks,

Liam

Paul Newcome

Happy to help! 👍️