Ask Your Question
0

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

Comments

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
1

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

=B2-A2

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

=B2-A2+(IF(CURRENT()<0;1;0))

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

Comments

2

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

Stats

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

Seen: 46 times

Last updated: Apr 24