cancel
Showing results for
Did you mean:

## Calculate Date and Time difference considering the weekends and workhours

Hi everybody!

I'm still learning how to use Power BI, I search for this everywhere but I didn't found an answer!

Here is the thing,

I need to calculate the difference between dates and time, but the thing is that I need to calculate just the working days and the workhours.

Taking the first line as an example: (03/11/2016 13:57:22 - 03/01/201613:36:38) the column hours should be 63:38:38

Considering that 03/05 and 06/05 are saturday and sunday, and considering that the work hours are from 08:00AM to 18:00PM.

How can I solve that!

Thank you!

2 ACCEPTED SOLUTIONS
Microsoft

@dcs136

The only possibility I can think of is that there is something wrong with the Calendar'[WorkDay] formula. Could you please check this column formula is like below? The data type of this column should be “True/False”.

If it is actually same as mine, could you please upload your .pbix file to OneDrive and share it with me? In that case I can take a look at your .pbix file and try to solve the problem.

Best Regards,

Herbert

Microsoft

@dcs136

How about the result if we update the “FirstDaySecDiff” measure as below?

```FirstDaySecDiff =
IF (
Table1[FirstDayEndTime] >= Table1[DateTimeFrom]
&& Table1[FirstDayEndTime] <= Table1[DateTimeTo],
DATEDIFF ( Table1[DateTimeFrom], Table1[FirstDayEndTime], SECOND ),
IF (
Table1[FirstDayEndTime] >= Table1[DateTimeFrom]
&& Table1[FirstDayEndTime] > Table1[DateTimeTo],
DATEDIFF ( Table1[DateTimeFrom], Table1[DateTimeTo], SECOND ),
0
)
)```

Best Regards,

Herbert

42 REPLIES 42

Hebert!

Thanks again!

It was exactly what you said.

The column Workday was duplicate.

Now it's working perfect!!

Announcements