# of days between two dates - week days only

[katc13840706]

Hi Everyone, I am currently using this expression to calculate the # of days between two dates: DATEDIFF(Entry Day,Actual Completion Date) I'd like it to ONLY count week days. Is there any way to filter out the weekend? Note: Entry Day is a custom date field: IF(ISBLANK(Entry Day),"",Entry Day) Thank you! Kat

[EricLu2]

Hi: DATEDIFF doesn’t give you the difference between the calendar days - it gives you the number of days between the two date-time values. https://support.workfront.com/hc/en-us/articles/217196767-Understanding-Calculated-Data-Expressions If the first date is “2017-01-01 16:00:00” and the second date is “2017-01-02 08:00:00” it will factor into consideration the times. In order to get the true calendar date difference, without consideration of the time associated with each time entry, you need to use CLEARTIME. Unfortunately, we haven’t been able to get CLEARTIME to work in the past seven months. I’ve had a ticket (1017069) open for seven months on this. If anyone knows how to clear the time off a date, without using CLEARTIME, I’d be indebted to also know. If you only want to count week days, don’t use DATEDIFF, use WEEKDAYDIFF (see help link above). It returns the number of week days between two dates taking into account the date and time associated with each date in the expression. If you don’t want it to factor in the time associated with each date, you’re back to the CLEARTIME issue. Thanks! Eric

[katc13840706]

Thank you, Eric! Really helpful!! Will make some adjustments :)

[AdinaPi1]

If you want to exclude holidays, there is a way to do that too, by providing the id of the Schedule you have in Setup -> Schedules WEEKDAYDIFF(Entry Date,Actual Completion Date,"521504260091b36b47f167f8571ae01c") ________________________________

[Doug_Den_Hoed__AtAppStore]

Hi Eric, Until CLEARDATE is working again, here is an alternative syntax: DATE(ROUND({entryDate},0)) You have 2 wishes left (and no wishing for more wishes). Regards, Doug

[Doug_Den_Hoed__AtAppStore]

Ahh...passing the ScheduleID into WEEKDAYDIFF: I'd forgotten that trick, Adina. Very slick! Thanks! Regards, Doug

[katc13840706]

Amazing! Will definitely apply the schedule reference to the custom field. Thank you Adina!

[AdinaPi1]

It looks like the formula I posted above does not work properly. It seems that the weekdaydiff function pretty much ignores the schedule even though I specify it. My original formula that worked which was using workminutesdiff, and came up with this newly revised formula: ROUND(WORKMINUTESDIFF(Entry Date,Actual Completion Date,"521504260091b36b47f167f8571ae01c")/(60)/(8),2) The 8 in the formula should match the number of working hours you have specified in the schedule (9 to 5 for example). Can someone try it and let me know if this works? I'm still trying to get the other way to work, but no luck yet.

[katc13840706]

Hi Adina, I gave this new formula a try. I tested it on projects that fell over the xmas holidays to see if the extra 3 non-work days in our schedule would be discounted. We use a 7.5 hour day so I adjusted the 8 in your formula. One of the two calculations seems to have counted the holidays, while the other one didn't. The field in the Screenshot with the 16 days should be around 13, but the 6 day calc is accurate. Going to run a few more tests...will let you know..