calculating worktime minus lunch

[svenne]

I'm trying to do a exceldocument where I fill in the time I start, th
time I finish minus how long lunch I had in hours or minutes. I als
need to calculate how much over or undertime I have worked, standar
hour is 8 hours.
here is an example how it could look, its the calculations that
important.


start end lunch totaltime
over/under-time
08.15 17.00 1 h 7 h 45 min -15

07.45 17.15 0.45 min 8 h 45 min +45



please ask if something is unclear.
th

 

[Bob Phillips]

Hi Svenne,

assuming that you record lunch as time (e.g. 1:00), then

D1: =B1-A1-C1
E1: =IF(D1<TIME(8,0,0),"-","")&TEXT(ABS(D1-TIME(8,0,0)),"hh:mm")

and copy down. But you cannot add up the E column to get the overtime, as
they are not numeric. You can get that with

=SUM(D15)-(COUNTA(D15)*TIME(8,0,0))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[David McRitchie]

Hi Svenne,
You will have to be consistent how you enter your time out for
lunch. In Excel you would enter h:mm but if you
want to use a decimal number you would have to divided that
by 24 to use in your calculations. I guess your example wasn't
decimal hours but you incorrectly used a decimal point with
minutes (.45min) break for lunch is about enough time to walk
down a couble of cubicles and back.

=B2-A2+(A2>B2)-C2 format as time as desired
appears the unambiguous format you want is [h] "hr" mm "min"
more conventional [h]:mm

More material in my
http://www.mvps.org/dmcritchie/excel/datetime.htm
--

 

[Harald Staff]

Hi

First: Enter time as time, with colons, not decimal points. See
http://www.cpearson.com/excel/datetime.htm on this.

Then see http://www.cpearson.com/excel/overtime.htm for a working sample.