calculating worktime minus lunch

  • Thread starter Thread starter svenne
  • Start date Start date
S

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
 
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(D1:D5)-(COUNTA(D1:D5)*TIME(8,0,0))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
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
--
 
Back
Top