Is there an alternative for WORKDAY.INTL in Smartsheets?

Liam Burnett

Hi,

I have a formula on excel that uses WORKDAY.INTL and it doesn't work in Smartsheets. I have made the formula so it only produces dates that are on a Tuesday, Wednesday and a Thursday. Is there another way to do this?

Thanks,

Liam

Paul Newcome

There should be. What is the logic you want to follow? If it is a Sunday or Monday, would it be the next Tuesday or the previous Thursday? What about Friday and Saturday?

Liam Burnett

Hi Paul,

The formula needs to only produce dates on Tuesdays, Wednesdays and Thursdays and if not then it moves the date to the next Tuesday. For the number of days in the current workday formula on excel is 3 days and I also have a holidays range to use which excludes general holidays.

Thanks,

Liam

Paul Newcome

And what is your original formula? Are you just taking a date and converting it, or are you trying to go out x number of days, or...?

The basics would be along the lines of...

=[Date Column]@row + IF(WEEKDAY([Date Column]@row) = 1, 2, IF(WEEKDAY([Date Column]@row) = 2, 1, IF(WEEKDAY([Date Column]@row) = 6, 4, IF(WEEKDAY([Date Column]@row) = 7, 3))))

The bold portion is simply a nested IF that generates a number of days to add to the date if the date happens to be a Sunday, Monday, Friday, or Saturday. Because we did not specify what to do if the weekday is a Tuesday, Wednesday, or Thursday, it won't add anything and will leave it as that date.

Liam Burnett

This is the original formula:

=IF(@cellAD="","ad reqd",IF(@cellAC="Stage 1",WORKDAY(@cellAD,-25,Holidays!Holiday),IF(@cellGW="","GW reqd",IF(@cellAC="Stage 2",WORKDAY.INTL(@cellGW,3,"1000111",Holidays!Holiday),""))))

Thanks,

Liam

Liam Burnett

Hi Paul,

Any Update on this?

Thanks,

Liam

Paul Newcome

Did you give the above a try?

Liam Burnett

Hi,

Sorry I am struggling to see where the formula you gave would fit into the one I entered below. I can't quite get my head around it. Probably me missing something :D

Thanks,

Liam

Paul Newcome

Did you test it?

Liam Burnett

I understand what your formula is doing I'm just not sure how to implement it with what I am using as the workday formula is producing the date.

Paul Newcome

What workday formula?

Liam Burnett

Hi Paul,

Sorry for the delayed response I have tried the formula you have provided and it mostly works, however, if the date doesn't need changing it goes to the week before. For example I have a date for the 23/06/20 which is a Tuesday that changes to the Tuesday before which is 16/06/20.

Paul Newcome

Can you copy/paste the formula from the sheet? The formula I provided above should not be doing that.

Liam Burnett

=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", [Gateway 4 (Date Format) - Hidden]5 + 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))))))))

The piece in bold is the formula you provided just with my columns.

Paul Newcome

It wouldn't be coming from the bold portion. It has an IF for weekdays 1, 2, 6, and 7. That would be Sunday, Monday, Friday, and Saturday. Tuesday, Wednesday, and Thursday dates should stay the same.

Can you show a screenshot of the formula not working that also shows your other columns referenced?

Liam Burnett

This is the formula and the date should be the 23rd which is a Tuesday so should be ok but it changes it to the 16. Below is the award date referenced in the formula and activity name is Stage 2. Sorry I cant screenshot the whole thing its a big sheet.