Duration per Day

[Anonymous]

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!

[v-cherch-msft · Accepted solution]

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