Workday function

Abhiraj Joshi

I want to calculate the working days from the start date. However, we have Sat as a working day which I have configured in the Gantt setting. (So we have 6 working days).

When I use the formula WORKDAY ([Start Date],38), from 3-Mar-2024, I get the output as 16-Apr-2024 - which is correct. 3-Mar-2024 being Sunday.

When I apply the same formula for 4-Mar-2024 (Monday), I get the output as 17-Apr-2024 - however, it should be 16-Apr-2024. as the number of working days from a 3-Mar & 4-Mar are the same.

Refer to the screenshot. Is my understanding correct?

Suppose there is going to be a difference in smartsheets calculation and actual working days. In that case, I will have to use nested IF statements based on the day of the week to calculate the working days and I have to do this for different sets of working days for different cities which becomes a complicated task.

ro.fei

@Abhiraj Joshi Thanks for the explanation, I think I understand what you're trying to achieve now! I think you might be happier using the predecessors & dependencies settings rather than the WORKDAY() formula.

Here's another example I set up using your dates (with some changes). I used Assigned Date for the start dates you have listed, & then used the Start Date column (in green) to enter the first actual working day for each task. From there I entered the Duration (38 days)--with Smartsheet's Project Settings that automatically calculated the End Date for each task (in blue). With this setup there is no variance & all the dates match your manual check dates automatically!

Here are the Project Settings I have on this sheet for reference. The Start Date & End Date columns are connected through the settings, & with dependencies & predecessors enabled as well as working days set up, date calculations can be almost entirely automated without formulas.

There are also a lot of other capabilities related to predecessors & dependencies, such as the ability to budget in lag or lead times & set different types of dependencies. I'm including a screenshot as I think they're fantastic to know about, & here's a link to a Help & Learning page with more details.

As a quick example, say you entered the 03/03 date directly in the Start Date like this:

Entering 2FF in the Predecessors column to indicate it should end at the same time as row 2 automatically switches the dates & brings them into line with your manual check.

I can share more but you'll probably find Smartsheet's self-training videos & other resources more useful (& luckily there are plenty available) but it seems like this may be the perfect solution for your case. The way it works can be a little tricky to figure out through trial & error so I really do recommend watching at least a couple of videos to understand the best ways to get started, but this is just the tip of the iceberg when it comes to the benefits of a setup like this--the critical path can be identified automatically, baseline tracking is available, etc. & it'd also allow you to change settings like the working days for your 10 cities without needing to rewrite crazy formulas.

I'll stop ranting but I hope this is helpful--this sounds like it would be extremely useful what you're trying to do! Again, let me know if you have any other questions about anything I've said here or want any additional links etc. Always happy to help however I can!

ro.fei

Hey there!! I just set up a test sheet with the settings & formulas you described & I have it working on my end:

My guess is the cells have different formulas in them. I used a column formula in my test sheet but that's not necessary, just be sure to drag down or copy in the same formula to all cells if you don't use the column formula.

To be safe I'd also double/triple check your project settings just to be sure you have Saturdays marked as working days since that's not a default. They should look similar to the photo below & your Gannt View should only have Sundays showing in the darker gray.

Let me know if either of those work, if not I'd be extremely curious as to what's happening! Hopefully one of these will solve though. Don't hesitate to @ me if you have any other questions!

Abhiraj Joshi

@ro.fei Thank you for making the effort and your response.

If you see - 03-Mar is a Sunday and if I consider it as my start date and calculate the working days, it would take the first working day as Monday (04-Mar).

Now if my start date is 04-Mar (Monday) and I calculate working days, my first working day should still be 04-Mar (Monday).

Thus the end date for both 03-Mar & 04-Mar should be same. But that's not the case through formula.

I checked my project settings as well as the formula and there is no issue with it.

In my screenshot attached - the pilot 38 column has the formula WORKDAY([Start Date]@row, 38) and the Date Check column is updated manually by me, calculating on a calendar.

If there is going to be this difference, then I have to apply the following formula to match the actual end date. This is based on the difference I am getting between formula and manual calculation.

=IF(OR(AND(WEEKDAY([Start Date]@row) > 1, WEEKDAY([Start Date]@row) < 6), WEEKDAY([Start Date]@row) = 7), WORKDAY([Start Date]@row, 38) - 1, IF(WEEKDAY([Start Date]@row) = 6, WORKDAY([Start Date]@row, 38) - 2, IF(WEEKDAY([Start Date]@row) = 1, WORKDAY([Start Date]@row, 38))))

Comparison in the following screenshot. (changed column names for better understanding)

The thing is I have to apply this formula to 10 different cities with different working days. Add to that the public holidays which I will update once I get this formula correct.

ro.fei

@Abhiraj Joshi Thanks for the explanation, I think I understand what you're trying to achieve now! I think you might be happier using the predecessors & dependencies settings rather than the WORKDAY() formula.

Here's another example I set up using your dates (with some changes). I used Assigned Date for the start dates you have listed, & then used the Start Date column (in green) to enter the first actual working day for each task. From there I entered the Duration (38 days)--with Smartsheet's Project Settings that automatically calculated the End Date for each task (in blue). With this setup there is no variance & all the dates match your manual check dates automatically!

Here are the Project Settings I have on this sheet for reference. The Start Date & End Date columns are connected through the settings, & with dependencies & predecessors enabled as well as working days set up, date calculations can be almost entirely automated without formulas.

There are also a lot of other capabilities related to predecessors & dependencies, such as the ability to budget in lag or lead times & set different types of dependencies. I'm including a screenshot as I think they're fantastic to know about, & here's a link to a Help & Learning page with more details.

As a quick example, say you entered the 03/03 date directly in the Start Date like this:

Entering 2FF in the Predecessors column to indicate it should end at the same time as row 2 automatically switches the dates & brings them into line with your manual check.

I can share more but you'll probably find Smartsheet's self-training videos & other resources more useful (& luckily there are plenty available) but it seems like this may be the perfect solution for your case. The way it works can be a little tricky to figure out through trial & error so I really do recommend watching at least a couple of videos to understand the best ways to get started, but this is just the tip of the iceberg when it comes to the benefits of a setup like this--the critical path can be identified automatically, baseline tracking is available, etc. & it'd also allow you to change settings like the working days for your 10 cities without needing to rewrite crazy formulas.

I'll stop ranting but I hope this is helpful--this sounds like it would be extremely useful what you're trying to do! Again, let me know if you have any other questions about anything I've said here or want any additional links etc. Always happy to help however I can!

Abhiraj Joshi

@ro.fei Thank you very much for the detailed explanation and the efforts. 🙏

I think the predecessor and dependencies will work. I will work on this.