Skip to main content
Solved

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

  • July 12, 2022
  • 9 replies
  • 300 views
B

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.

Best answer by kuovonne

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})


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.

9 replies

TheTimeSavingCo
Forum|alt.badge.img+31

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

Adam, Airtable Consultant - thetimesaving.company
B
  • Bharat_Singhal
  • Author
  • Participating Frequently
  • July 12, 2022

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

TheTimeSavingCo
Forum|alt.badge.img+31

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


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

Adam, Airtable Consultant - thetimesaving.company
B
  • Bharat_Singhal
  • Author
  • Participating Frequently
  • July 12, 2022

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

TheTimeSavingCo
Forum|alt.badge.img+31

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


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

Adam, Airtable Consultant - thetimesaving.company
kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • July 12, 2022

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.

B
  • Bharat_Singhal
  • Author
  • Participating Frequently
  • July 13, 2022

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.


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
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • Answer
  • July 13, 2022

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})


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.

B
ScottWorld
TheTimeSavingCo
B
  • Bharat_Singhal
  • Author
  • Participating Frequently
  • July 14, 2022

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.


thank you so much! this is super helpful.

kuovonne
Terms & ConditionsAccessibility statement