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

[n00bquestions]

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.

[acknak]

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.

[MrProgrammer]

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.

[d3ndr1t3]

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?

[thinman3]

Does this work for you ?

Hours worked & overtime # 1.ods

(12.53 KiB) Downloaded 5011 times

thinman3

[Richarda44]

To get the 6th column to calculate overtime, how about: IF(hours worked>8;hours worked - 8;0)

[collinjn81]

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

[thinman3]

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

[Mrwizard]

So, this worked for me: A = IN B = OUT | C = IN D = OUT | E = HOURS WORKED. Duplicate your in/out hours from A & B to D & E and make the cell format TIME = 1:37PM then enter this formula into E =+(((D6-C6)+(F6-E6))*24)/2 Works like a charm then just format column E to number 2 decimal points, then you can sum that column for total hours worked then you can perform calc on that total field for your OT using your regular hour total which is constant. In my case it's 80 hours so E total - 80 = OT