[Solved] Calculate elapsed time from start/end time

[norman6001]

Hi, I am using Open Office 4.1.1 on my PC which has windows 7 installed. I am creating a spreadsheet which lists the start and end times of events, and wish to be able to have a third column displaying elapsed time in hours and minutes. Sometimes the start and finish times will straddle midnight (eg 10.05 pm to 02.33 am). I am fairly new to using calc like this, but when I worked in an office years ago I used Excel, but have not been able to recall how to do this. I wonder if anyone can help, please? Thanks in advance.

[acknak]

Greetings and welcome to the community forum!

As long as the elapsed time will always be less than one day (times are stored as days), you can use something like this:
=END-START+(END<START)

The last bit produces either zero (false) or one (true), which corrects the difference for cases where END is less than START.

If you might have elapsed times greater than one day, you have to provide dates as well as times, and in that case, you can just subtract: the END will always be greater than the START.

[norman6001]

Hi, and thanks for your speedy reply. However, it does not give the results I expected. I used END (14.02) and START (05.12) and expected to get 8.50 ( 8 hours and 50 minutes) Instead, I get 21.36. Can you advise what I am doing incorrectly, please?

[RoryOF]

Use : instead of . as the hours/mins separator.

[acknak]

Use : instead of . as the hours/mins separator.

Right--Good work to catch that!

I meant to mention that you have to enter the times in a way that Calc recognizes as a time: h<colon>m (for most locales; could be different for yours)

The 'h' and 'm' (hours and minutes) can be most any integer value. You can omit either one.

[norman6001]

Thanks to both of you guys, I can now get and understand the answers!!!

[acknak]

... I used END (14.02) and START (05.12) and expected to get 8.50 ( 8 hours and 50 minutes) Instead, I get 21.36. ...

Calc expects times stored as a decimal number of days. So here you've subtracted (5 12/100) from (14 2/100) to give (8 90/100). When the result is formatted as a time, only the decimal part is used and 9/10 days is displayed as 21 hours, 36 minutes.

When you enter or do arithmetic with times, Calc will automatically use a clock time number format. For elapsed times, you have to manually apply an elapsed time format in place of the default clock time format: [H]:MM is the most common elapsed time format.

[ncdm]

Dang, so... I have two cells that are using time format
10:15:00 AM 11:43:00 AM

Then I use =END-START by clicking the two columns and get 01:28:00 AM
How do I get that result to say 01:28:00 instead of AM, no biggie

Also, I had to manually click each cell each time to make the formula work, can't we just copy and past a formula to calculate endless time entries, seems silly to have to manually click through each formula cel to get a result column

Mac Sierra 10.12.6 - And OpenOffice 4.1.2

Thanks

[RusselB]

For the formatting, simply use Format -> Cells (Ctrl+1) to bring up the cell formatting dialog,
As to copying the formula, once you have it entered, and with the format you want, simply highlight the cell, then press Ctrl+c to copy the formula to your clipboard.
Place the cursor on the cell that you want the formula in (highlighting the cells if more than one) and press Ctrl+v to paste the formula and the format into the new location.
As long as the cell references in the formula are not absolute (do not have the $ in them), then the cell references will automatically update relative to the pasted location.

[robleyd]

Then I use =END-START by clicking the two columns and get 01:28:00 AM
How do I get that result to say 01:28:00 instead of AM, no biggie

Select the cells you want to display in that format; go to FORMAT | CELLS |NUMBERS | TIME and select an existing format that is what you want, or create your own custom time format; click the Help button for more info.

Also, I had to manually click each cell each time to make the formula work, can't we just copy and past a formula to calculate endless time entries, seems silly to have to manually click through each formula cel to get a result column

You could simply create the formula in one cell, then copy and paste to the range needed; or drag the black square on the bottom right of the cell.

If you are new to spreadsheets you might find [Tutorial] Ten concepts that every Calc user should know a useful resource.

[ncdm]

FORMAT | CELLS |NUMBERS | TIME and select an existing format that is what you want

Excellent, looks like we got it...
appreciate the quick reply everyone.

for some reason the first time I did - =SUM(C17-B17) - it screwed up, now it's happy.
Jim

[Villeroy]

=C17-B17 gives one resulting number. The SUM of this single number is the same. Therefore using the SUM function is obsolete.
Nothing screwed up. The result 0.0611111 is the correct result when you subtract 11:43 from 10:15.
Actually you subtract 0.4881944 from 0.4270833 as you can see when you remove the special time format from C17 and B17.
Spreadsheets do not know any time values. A time value is a number that represents a fraction of a day.
0.4881944 of a day is 11:43 (almost half of the day)
0.4270833 of a day is 10:15
The difference is 0.061111 of a day which is 01:28
You can format these numbers any way you want without changing the numeric cell value. 01:28 is just a different representation of the cell value 0.061111