Another Time Calulation Question - Sorry.

[Dugster]

Hi, I hope you don't mind a newbie asking newbie questions? I'm a filling
short of a sandwich when it comes to excel, so please keep that in mind if
you decide to be so kind as to respond. Thanks!

I am trying to get the total hours and minutes worked over two periods to
show in columns I and J. Honest, I've used the help file and looked about
here but I just don't get it. :s I'd appreciate all and any help. Thanks.

A B C D E F G H I J
1 AM AM PM PM Deficit B/F
2 Start Finish Start Finish
3 Hrs Mins Hrs | Mins
4 09 | 10 12 | 30 13 | 00 17 | 00
5 08 | 30 12 | 30 13 | 00 17 | 30
6 09 | 00 12 | 00 12 | 40 17 | 00

 

[Flick Olmsford]

Instead of the way you are doing it, remember that Excel stores dates and
times as numbers. Dates as an integer (the number of dates since Jan 1st
1900?), and times as a fraction (8AM = .3333...)

Simply subtract the start time from the end time. Also subtract any time
off for lunch and breaks that should not be counted.

The result might look funny as Excel might format the resulting cell as a
date. Format THAT cell as a number and you'll have your elapsed time.

 

[Flick Olmsford]

Actually, do this extra step - multiply by 24 (hours per day). The resulting
formula should look like

=(E14-D14)*24

where E14 is the end time, D14 is the start time

 

[Ron Coderre]

If you really need to use that structure for recording time...
try this:

I4: =INT((TIME(G4,H4,0)-TIME(E4,F4,0)+TIME(C4,D4,0)-TIME(A4,B4,0))*24)
J4: =MOD((TIME(G4,H4,0)-TIME(E4,F4,0)+TIME(C4,D4,0)-TIME(A4,B4,0))*24,1)*60

copy those formulas down as far as you need.

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Dugster]

Thanks Ron, The minutes are calculating a treat (J4) The hours though
(I4) is returning all zero's.

But thanks for what I got so far.

 

[Dugster]

Thanks Flick. I just didn't get it though. I did say I was a filling short
of a sandwich though.

 

[Dugster]

Hi again Ron, ignore my last message; I just changed the cell format and it's
perfect.

Thank you very much for your help. You guys are great.

Doug

 

[Ron Coderre]

Thanks for the update, Doug...I'm glad we could help.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)