Ask Your Question

Summing Hours as duration not correct

asked 2020-04-23 22:37:38 +0200

pete28 gravatar image

updated 2020-04-23 22:46:08 +0200

I am having a time sheet where it monitors the start date and time with the end date and time . I have successfully managed to subtract the end time from the start time and get the duration . I want to calculate the duration and I am using the sum for that . I have formatted my duration cells to be of [H]:MM:SS and my start and end time formats are HH:MM:SS however when summing up the duration the number is not correct when the duration is passing midnight : An example: from 23:00:00 (11:00 PM ) to 01:00:00 (01:00 AM) This 2 hours duration is not summed

edit retag flag offensive close merge delete


Please edit your question and attach a sample file, so someone can test the issue.

m.a.riosv gravatar imagem.a.riosv ( 2020-04-23 22:55:55 +0200 )edit

I want to calculate the duration and I am using the sum

I don't understand that - a duration isn't the sum but the difference of two times. And of course you'll get -22:00 hours for the duration, if you don't tell LibreOffce that 23:00:00 (11:00 PM ) is meant to be a time of the day before the of day of time 01:00:00 (01:00 AM).

image description

Opaque gravatar imageOpaque ( 2020-04-23 23:13:35 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-04-24 07:43:58 +0200

JohnSUN gravatar image

When calculating the duration, you took the first step correctly - the difference between the end time and the start time.

If you use the example of colleagues @Opaque formula that he showed


Now just adjust the result for cases where midnight falls into the interval


If two or more midnight fall in the interval, you will not be able to get the correct result without indicating the date

edit flag offensive delete link more



I'd rather use =B2-A2+(A2>B2) than CURRENT().

erAck gravatar imageerAck ( 2020-04-24 16:23:45 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-04-23 22:37:38 +0200

Seen: 46 times

Last updated: Apr 24