Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

difference between two time stamps

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_dateshift_start_timeshift_end_timeworking_hours
5-Apr-216: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's avatar
    SivaMani
    5 years ago

    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)

     

7 Replies

  • SivaMani's avatar
    SivaMani
    Icon for Resident Rockstar rankResident Rockstar
    5 years ago

    Anonymous, what is the data type of the columns?

     

    shift_end_time - will only have the time without a date?

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        SivaMani
        Icon for Resident Rockstar rankResident Rockstar
        5 years ago

        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)