Forum Discussion
Calculate hours between two date/time columns and exclude weekends
Hi all, first post here so be nice since im a beginner. 🙏
Im trying to caculate the hours between two columns including start date/time and end date/time and I want to exlude the weekends. I have been looking but many questions is about calculate business hours but that's not the case for me.
Can you guys please help a newbie out? 🙂 Thanks!!
you can identify the number of weekends between the 2 dates via the below thread :
And via datediff get the hours count between the 2 dates and substract the hours based on number of weekends * 24
4 Replies
- NandanHegde
Super User
You can identify whether a date is weekday or weekend bbbby below aspect :
if(WEEKDAY([Date],2)>=6,"Weekend","Weekday")
And you can use DATEDIFF to identify the diff in hours between 2 dates:
Can you also plz state what you mean by exclude weekends
Thanks for the reply.
Each row is basicly a ticket with a start and end-date/time (Opened and solved) and I want to calculate the number of hours it takes so solve every ticket. And by weekend I mean I want to exclude the hours from saturday and sunday since we are not measured in the SLA on the weekends. Make this more sense now?Im familiar with the DATEDIFF formula but if a ticket is opened on a friday and solved on a monday, it will include the hours from the weekend and I don't want it to include the hours from the weekend.
- NandanHegde
you can identify the number of weekends between the 2 dates via the below thread :
And via datediff get the hours count between the 2 dates and substract the hours based on number of weekends * 24
Thanks! Great way to solve it.
I used this formula:Custom = DATEDIFF('MyTable'[Start date],'MyTable'[End date],HOUR) -COUNTROWS(FILTER(CALENDAR('MyTable'[Start date],'MyTable'[End date]), WEEKDAY([Date],2) in {6,7} )) * 24
