WORKDAY_DIFF function and DATETIME_DIFF function are giving same output. Am I doing something wrong

[Bharat_Singhal]

Hello everyone,

I am trying to use workday_diff function to calculate whether my team is able to process orders within specified TAT or not. and hence needs to calculate workdays.

We have a column that tracks date created ({Date Created})for any order and another column that tracks last modified time to track how much time we took to finish the order ({Date Completed}).

Hence the formula is :
WORKDAY_DIFF({Date Created},{Date Completed})

But the output counts weekends too and the output is same as using DATETIME_DIFF. Please help me figure it out.

[TheTimeSavingCo]

Hi Bharat, that’s super weird. Could I get a screenshot of this happening so I could attempt to recreate it please?

[Bharat_Singhal]

as you can see the outcome of both formulas is same. However, Workday diff should skip 9 and 10 July 2022.

[TheTimeSavingCo]

Could you also include the Date Completed field in your screenshot please?

[Bharat_Singhal]

Actually date completed field is not relevant as none of these tickets have been completed yet

[TheTimeSavingCo]

Hi Bharat, sure that’s fine, I just need to see your current Date Completed values so I can attempt to recreate your setup and troubleshoot it

[kuovonne]

Bharat_Singhal:

Actually date completed field is not relevant as none of these tickets have been completed yet

Can you share screen shots of the actual configuration of all three formulas involved?

There is something amiss in them. The fields might not have the formulas that their names imply.

[Bharat_Singhal]

So the screenshot looks like this :

And formulas are as follows :
Column 1 - Autocalculated when record is created
Column 2 - Autocalculated when the record’s ‘Status’ field is updated (‘Status’ column is not visible in the screenshot)
Column 3 - DATETIME_DIFF(TODAY(),{Date Created},‘d’)
Column 4 - DATETIME_DIFF({Date Completed},{Date Created},‘d’)
Column 5 - WORKDAY_DIFF({Date Created},TODAY())
Column 6 - WORKDAY_DIFF({Date Created},{Date Completed})

[kuovonne · Accepted solution]

Thank you for the screen shots and the complete formulas.

The differences you are seeing are related to how DATETIME_DIFF() and WORKDAY_DIFF() work in relation to partial days, and the fact that TODAY() has a time of midnight GMT.

WORKDAY_DIFF() includes both the beginning and ending date in the calculation, but exclude weekends. TODAY() is currently July 13. From July 4 to July 13, WORDAY_DIFF() counts the following eight days: 4, 5, 6, 7, 8, 11,12, 13, omitting the 9th and 10th which are the weekend.

DATETIME_DIFF() only includes whole days, not partial days, based on a time of GMT midnight for July 13. This gives the following eight days: 5, 6, 7, 8, 9, 10, 11, 12. The 4th is not included because it is a partial day. The 13th is not included because TODAY() has a time of GMT midnight, and thus there are no hours on the 13th to include.

You can see this difference between DATETIME_DIFF() and WORKDAY_DIFF() by using the same date as the start and end date in both formulas. If the date is a weekday, DATETIME_DIFF() will return 0, but WORKDAY_DIFF() will return 1.

If you wait until another weekend, you should start to see a difference in the formula results.

You may also want to replace TODAY() with NOW() to see if it gives results closer to what you want.

[Bharat_Singhal]

thank you so much! this is super helpful.