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

Discuss the spreadsheet application

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

Postby n00bquestions » Fri Dec 07, 2012 7:49 pm

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."

Thanks in advance.
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

Postby acknak » Fri Dec 07, 2012 8:23 pm

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
User avatar
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

Postby MrProgrammer » Fri Dec 07, 2012 10:40 pm

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).
User avatar
MrProgrammer
Moderator
 
Posts: 3929
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Resurrection!

Postby d3ndr1t3 » Wed Oct 30, 2013 6:54 am

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

Postby thinman3 » Wed Oct 30, 2013 9:24 pm

Does this work for you ?

Hours worked & overtime # 1.ods
(12.53 KiB) Downloaded 4617 times


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

Postby Richarda44 » Wed Oct 30, 2013 10:40 pm

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

Postby collinjn81 » Mon Nov 04, 2013 3:10 am

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
Operating system: Window Vista Business
collinjn81
 
Posts: 28
Joined: Thu May 19, 2011 9:38 am

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

Postby thinman3 » Mon Nov 04, 2013 6:38 am

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


Return to Calc

Who is online

Users browsing this forum: No registered users and 13 guests