Forum Discussion

Advocate I

10 years ago

Solved

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 tim...

Need Help

v-haibl-msft
10 years ago
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

v-haibl-msft

9 years ago

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

v-haibl-msft

Microsoft Employee

10 years ago

dcs136

There may be several methods to get the expected result. I’ll divide the difference to three parts, the first day, the middle days and the last day. For details, please refer to following steps.

I’ve also upload my .pbix file here for reference.

Create a calendar table with following formula. But do not create relationship between these two tables.
```
Calendar =
CALENDAR ( "1/1/2016", "12/31/2016" )
```

Create a column in calendar table to mark the working days.

WorkDay = 
VAR WeekDayNum =
    WEEKDAY ( 'Calendar'[Date], 2 )
RETURN
    (
        IF ( WeekDayNum = 6 || WeekDayNum = 7, FALSE (), TRUE () )
    )

Create a column to store the working end time of first day.

FirstDayEndTime = 
DATE ( YEAR ( Table1[DateTimeFrom] ), MONTH ( Table1[DateTimeFrom] ), DAY ( Table1[DateTimeFrom] ) )
& " 18:00:00"

Create a column to calculate the working seconds of first day.

FirstDaySecDiff = 
DATEDIFF ( Table1[DateTimeFrom], Table1[FirstDayEndTime], SECOND )

Create a column to store the working start time of last day.

LastDayStartTime = 
DATE ( YEAR ( Table1[DateTimeTo] ), MONTH ( Table1[DateTimeTo] ), DAY ( Table1[DateTimeTo] ) )
& " 8:00:00"

Create a column to store the working end time of last day.

LastDayEndTime = 
DATE ( YEAR ( Table1[DateTimeTo] ), MONTH ( Table1[DateTimeTo] ), DAY ( Table1[DateTimeTo] ) )
& " 18:00:00"

Create a column to calculate the working seconds of last day.

LastDaySecDiff = 
IF (
    FORMAT ( Table1[DateTimeFrom], "Short Date" )
        <> FORMAT ( Table1[DateTimeTo], "Short Date" ),
    IF (
        Table1[DateTimeTo] >= Table1[LastDayStartTime]
            && Table1[DateTimeTo] <= Table1[LastDayEndTime],
        DATEDIFF ( Table1[LastDayStartTime], Table1[DateTimeTo], SECOND ),
        IF (
            Table1[DateTimeTo] > Table1[LastDayEndTime],
            DATEDIFF ( Table1[LastDayStartTime], Table1[LastDayEndTime], SECOND ),
            0
        )
    ),
    0
)

Create a column to calculate the working seconds of middle days.

MidDaysSecDiff = 
IF (
    FORMAT ( Table1[DateTimeFrom], "Short Date" )
        <> FORMAT ( Table1[DateTimeTo], "Short Date" ),
    3600 * 10
        * (
            CALCULATE (
                DISTINCTCOUNT ( 'Calendar'[Date] ),
                FILTER (
                    'Calendar',
                    'Calendar'[Date] > Table1[FirstDayEndTime]
                        && 'Calendar'[Date] < Table1[LastDayStartTime]
                        && 'Calendar'[WorkDay] = TRUE ()
                )
            )
                - 1
        ),
    0
)

Create the final column to calculate the total working hours.

TotalHourDiff = 
 ( Table1[FirstDaySecDiff] + Table1[LastDaySecDiff]
    + Table1[MidDaysSecDiff] )
/ 3600

Note: The data type of columns of “FirstDayEndTime”, “LastDayStartTime” and “LastDayEndTime” should be Date/Time as below.

Best Regards,

Herbert

dcs136
Advocate I
10 years ago
Herbert,

Thank you so much for your help.

But unfortunately DATEDIFF gives an error "In DATEDIFF function, the start date cannot be greater than the end date"
because some hours on "DateTimeFrom" are greater than the "FirstDayEndTime" as you can see on the picture

I tried to fix this, but I couldn't do it.

Thank you!
- v-haibl-msft
  Microsoft Employee
  10 years ago
  dcs136
  
  Please update the DAX formula of FirstDaySecDiff column as below and have a try again.
  
  FirstDaySecDiff = IF ( Table1[FirstDayEndTime] >= Table1[DateTimeFrom], DATEDIFF ( Table1[DateTimeFrom], Table1[FirstDayEndTime], SECOND ), 0 )
  Best Regards,
  Herbert
  - dcs136
    Advocate I
    10 years ago
    Herbert,
    
    Thanks again for your help and sorry for bothering you again,
    
    The last problem was solved, but I still have a problem with the MidDaysSecDiff column.
    As you can see on the screen below, It gives an erro saying that DAX can't compare date values with True/False values.
tmears
Helper III
9 years ago
thank you for contributing, this is excately what i was looking for. One quick question, we have different opening and closing hours for certain customers, do anyone have any suggestions on how i could overcome this issue:

ie customer A is 09:00 to 17:00
Customer b is 08:30 to 17:30 etc etc

Any help would be very much appriciated
christianfcbmx
Post Patron
8 years ago
This solution present errors in the following cases!

Can sombody share a solution??? (the pbix and the excel data source will be available here):
https://www.dropbox.com/s/5b4zmo40k9dsiwl/time%20difference%20considering%20only%20friday%20to%20monday%20only%20workhours.pbix?dl=0
tmears
Helper III
8 years ago
wonder if anyone could help? I have the following:

MidDaysSecDiff =
IF (
    FORMAT ( msdyn_audithistories[Start time], "Short Date" )
        <> FORMAT ( msdyn_audithistories[createdon], "Short Date" ),
    3600 * 10
        * (
            CALCULATE (
                DISTINCTCOUNT ( 'Calender'[Date] ),
                FILTER (
                    'Calender',
                    'Calender'[Date] > msdyn_audithistories[FirstDayEndTime]
                        && 'Calender'[Date] < msdyn_audithistories[LastDayStartTime]
                        && 'Calender'[WorkDay] = TRUE ()
                )
            )
                - 1
        ),
    0
)

However i need to state that if the msdyn_audithistories[Start time] ie the start time is blank that it returns 0? any pointers?

Many thanks
Anonymous
Not applicable
7 years ago
Hi!
Thank you for your help!!
I think there is a mistake in your solution.

Formula for FirstDaySecDiff is only correct if DateTimeFrom's date is different from DateTimeTo's date

FirstDaySecDiff = DATEDIFF ( Table1[DateTimeFrom], Table1[FirstDayEndTime], SECOND )

Otherwise the right formula is as follows:

FirstDaySecDiff =
IF (
FORMAT (Table1[DateTimeFrom], “Short Time”) = FORMAT (Table1[DateTimeTo], “Short Time”),
DATEDIFF ( Table1[DateTimeFrom], Table1[DateTimeTo], SECOND ),
DATEDIFF ( Table1[DateTimeFrom], Table1[FirstDayEndTime], SECOND )
)

I tried with this new formula and now i get right calculation results.

Best regards,
Andrés
- Anonymous
  Not applicable
  6 years ago
  If it is helpful, I believe I found a solution that can be implemented without using reference tables. It also applies to business hours Monday to Friday only :
  https://community.powerbi.com/t5/Desktop/Calculating-Working-hours/m-p/1233459/highlight/true#M546593

Forum Discussion

Calculate Date and Time difference considering the weekends and workhours

Related content

Dynamic Calculation Different Period

Different Calculations

Difference date calculation considering holidays per counties

Calculating Differences

How to add two different calculated columns of two different table