Hi Bharat, that’s super weird. Could I get a screenshot of this happening so I could attempt to recreate it please?
as you can see the outcome of both formulas is same. However, Workday diff should skip 9 and 10 July 2022.
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?
Actually date completed field is not relevant as none of these tickets have been completed yet
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
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.
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})
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.
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.
