Calculating hours worked in Excel? 2

[psybertek]

Hello all,

I know there is a way to calculate hours worked in excel, but I am having a dificult time finding the formula. I need to subtract the stop time from the start time. I keep getting ######## in the cell. I have been thru the help files and I can find no mention of a specific format or formula. Any help would be greatly appreciated.


Psybertek
"Computer Junkies never die, They just upload."

 

[sfvb]

Usually, ######## means that you need to widen the column.

 

[plantj]

Try this in a new sheet

A1: Start
A2: Finish
A3: Worked
B1: 09:00
B2: 17:00
B3: =Finish-Start

B3 should show 08:00

That should give you some confidence

 

[fneily]

Let's say cell A1 has start time 22:00 (10:00 pm) and cell B1 has stop time 6:00 (6:00 am). If you do in cell C1,
=B1-A1 you get ###### because the substraction is a negative. This is because a time without a date has a date serial number of 0. You can not have negative serial numbers using the 1900 date system. If the hours are in the same day, you can use =ABS(B1-A1) since the direction of time doesn't matter. However, if you cross midnight, 22:00 is in one day and 6:00 is the next morning, ABS doesn't give the right answer. You can then use =MOD(B1-A1,1) in cell C1. Format cell C1 by clicking Format, Cell, Time, 37:30:55.

Neil

 

[psybertek]

plantJ and fneily,
Thank you both for your input. I used a combination of both to calculate my hours. I now find I have another problem, and I was hoping you might be able to point me in the right direction again. I need to figure total hours for the week. In example ........
Start End Total
Monday 7:00 am 5:30 pm 10:30
Tuesday 7:00 am 6:30 pm 11:30

Total Hrs 22:00

Psybertek
"Computer Junkies never die, They just upload."

 

[Zathras]

If you are enter your times just the way they appear in your post, then Excel will correctly interpret the data as a time value and automatically format the cell as Time which is the same as assigning the format of

 h:mm AM/PM

-- All is well and good.

Now if your time values are in [blue] B2:C3 [/color] then all you have to do is set up column "D" like this (Don't worry about what it looks like as you are entering the formulas. You will set a format for the column when you are done.):
[blue]

D1: 'Total
D2: =C2-B2
D3: =C3-B3
D5: =SUM(D2:D3)

[/color]
Now select column D and do a right-click and choose "Format Cells..."
In the list where is says "Category" choose "Custom"
In the text box where it says "Type" enter (or paste)
[blue]

     [hh]:mm

[/color]
and click OK. You should see exactly what you want to see.