[Solved] Calculate 9AM - 5PM = 8 hours? Formula please

[Solved] Calculate 9AM - 5PM = 8 hours? Formula please

This has got to be much easier than what I've come across so far. Basically I have 3 columns (start time, finish time, hours worked). I'd prefer to keep my times in AM/PM format not military. How can I get the hours worked (such as 8, 7.25, etc.) from subtracting the start time from the finish?

BONUS question: I would like a 4th column for overtime. It can be a simple if statement. Just looking for If hours worked > 8 then display "YES."

Last edited by MrProgrammer on Sat Jul 18, 2020 4:52 am, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Open Office 3 on Windows 7
n00bquestions

Posts: 3
Joined: Fri Dec 07, 2012 7:44 pm

Re: How can I calculate 9AM - 5PM = 8 hours? Formula please

What have you tried? Did you try something that didn't work? What did you get?

See http://wiki.openoffice.org/wiki/Documen ... e_overview

If column A is start, B: end, both as time values with any format you like, then C: end-start will give you the time worked, as a number of days. To get hours from that, multiply by the number of hours/day: C: (end-start)*24

If you ever have work shifts that extend across midnight, you have to adjust the formula: (end-start+(end<start))*24

For the overtime, I'd use simply hours>8 (gives zero or one) and then use the cell format to display the value as desired.
AOO4/LO5 • Linux • Fedora 23

acknak
Moderator

Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: How can I calculate 9AM - 5PM = 8 hours? Formula please

Hi, and welcome to the forum.

Read section 4. Times in cells in Ten concepts that every Calc user should know. There are many "noob" answers there if you read the entire tutorial. It will save you hours of time struggling with Calc.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).

MrProgrammer
Moderator

Posts: 3940
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Resurrection!

I'm working on a similar spreadsheet and found this thread on a google search. The spreadsheet I'm trying to create is meant to be user friendly so employees may enter their own hours.

I have had success calculating the number of hours by doing the following:

Create four columns for time entry. Column A1 = Time in, Column B1 = Time out, Column C1 = Time in, Column D1 = Time out.

All columns, including the next one, are set to the following format: Time / 01:37 PM

Column E1 totals the number of hours entered for the day.

SUM=(A1-B1)+(C1-D1)

I am trying to get a sixth column to count the number of overtime hours (anything in excess of 8), without success so far. Any suggestions?
OpenOffice 4.0 Windows XP Pro
d3ndr1t3

Posts: 1
Joined: Wed Oct 30, 2013 6:16 am

Re: How can I calculate 9AM - 5PM = 8 hours? Formula please

Does this work for you ?

Hours worked & overtime # 1.ods

thinman3
3.4.1 & 4.1.5 on MS Windows 7 Pro x64
thinman3
Volunteer

Posts: 382
Joined: Sat Jul 11, 2009 8:53 pm

Re: How can I calculate 9AM - 5PM = 8 hours? Formula please

To get the 6th column to calculate overtime, how about: IF(hours worked>8;hours worked - 8;0)
OOo 4.1.1. bld 9593 on MS Windows 10 64 SP1 Chillblast fusion i5 and Acer Aspire E1-572 Laptop

There's got to be a better way
And for all accountants - The change is coming
Richarda44

Posts: 351
Joined: Sat May 24, 2008 6:59 pm

Re: How can I calculate 9AM - 5PM = 8 hours? Formula please

How to get "blank value" if there no time out?

For example:
In (08:00) Out ()

If i used formula (A2-A1), it will show "-8:00".
Open office 4.0.1
collinjn81

Posts: 28
Joined: Thu May 19, 2011 9:38 am

Re: How can I calculate 9AM - 5PM = 8 hours? Formula please

In the cell where you are showing the -08:00 time, enter the formula below and it should produce a "blank" cell if no END TIME is recorded.

=IF(A2="";"";A2-A1)

thinman3
3.4.1 & 4.1.5 on MS Windows 7 Pro x64
thinman3
Volunteer

Posts: 382
Joined: Sat Jul 11, 2009 8:53 pm