Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Duration per Day

Hello,

 

I have a start date / time & an end date / time. the duration is calculated out but I need a formula that will provide me with the duration per day. IE the first row begins on the 16th and ends on the 17th. I need the duration on the 16th (1h 16m) and the 17th (2h 6m) calculated.

 

Please let me know if possible.

 

Thanks!

 

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous

     

    You may create two calculated columns to get the value.

    Column =
VAR _day =
    DATE ( YEAR ( 'Table'[END] ), MONTH ( 'Table'[END] ), DAY ( 'Table'[END] ) )
RETURN
    IF (
        DATEVALUE ( 'Table'[END] ) <> DATEVALUE ( 'Table'[Start] ),
        _day - 'Table'[Start],
        'Table'[END] - 'Table'[Start]
    )

    Column 2 = var _day=DATE(YEAR('Table'[END]),MONTH('Table'[END]),DAY('Table'[END])) return IF(DATEVALUE('Table'[END])<>DATEVALUE('Table'[Start]),'Table'[END]-_day)

    Regards,

    Cherie

     

     

1 Reply

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous

     

    You may create two calculated columns to get the value.

    Column =
VAR _day =
    DATE ( YEAR ( 'Table'[END] ), MONTH ( 'Table'[END] ), DAY ( 'Table'[END] ) )
RETURN
    IF (
        DATEVALUE ( 'Table'[END] ) <> DATEVALUE ( 'Table'[Start] ),
        _day - 'Table'[Start],
        'Table'[END] - 'Table'[Start]
    )

    Column 2 = var _day=DATE(YEAR('Table'[END]),MONTH('Table'[END]),DAY('Table'[END])) return IF(DATEVALUE('Table'[END])<>DATEVALUE('Table'[Start]),'Table'[END]-_day)

    Regards,

    Cherie