difference between two time stamps

[Anonymous]

Hi Community, 

Please help with finding a duration between two time stamps.  

I have the 3 columns, i.e., shift_date, shift_start_time, shift_end_time.  As this is an evening shift, the endtime mostly falls the next day.

shift_date	shift_start_time	shift_end_time	working_hours
5-Apr-21	6:00:00 PM	2:30:00 AM	16

I tried using 

working_hours= DATEDIFF( 'table_1'[shift_start_time], 'table1'[shift_end_time], HOUR),

but it is not working for me.  

Any reference to documents is also appreciated.

Thanks and Regards

Sabyasachi

[SivaMani]

@Anonymous, what is the data type of the columns?

shift_end_time - will only have the time without a date?

[Anonymous]

Thank yo @SivaMani for looking into this.  Here, data type is time (h:nn:ss AM/PM).  Yes, the shift end time without a date, its always either the shift day or next day.

Regards

Sabyasachi

[SivaMani]

@Anonymous,

Try the below measure,

WorkingHrs =

VAR __StartTime = MAX('Table (2)'[shift_date]) + MAX('Table (2)'[shift_start_time])

VAR __EndTime = IF(MAX('Table (2)'[shift_start_time])>MAX('Table (2)'[shift_end_time]),MAX('Table (2)'[shift_date])+1+MAX('Table (2)'[shift_end_time]),MAX('Table (2)'[shift_date])+MAX('Table (2)'[shift_end_time]))

RETURN

DATEDIFF(__StartTime,__EndTime,HOUR)

[Anonymous]

@SivaMani I am getting the output as rounded value even when we expect a fraction.

[SivaMani · Accepted solution]

@Anonymous,

This would work, 

WorkingHrs =

VAR __StartTime = MAX('Table (2)'[shift_date]) + MAX('Table (2)'[shift_start_time])

VAR __EndTime = IF(MAX('Table (2)'[shift_start_time])>MAX('Table (2)'[shift_end_time]),MAX('Table (2)'[shift_date])+1+MAX('Table (2)'[shift_end_time]),MAX('Table (2)'[shift_date])+MAX('Table (2)'[shift_end_time]))

RETURN

DIVIDE(DATEDIFF(__StartTime,__EndTime,MINUTE),60)

[Anonymous]

@SivaMani awesome, thanks a lot

[SivaMani]

@Anonymous, You're welcome! I am glad that it worked.