cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JPitChevron Frequent Visitor
Frequent Visitor

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!

 

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Duration per Day

Hi @JPitChevron

 

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)

1.png

Regards,

Cherie

 

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support Team
Community Support Team

Re: Duration per Day

Hi @JPitChevron

 

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)

1.png

Regards,

Cherie

 

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.