Timesheet troubles

  • Thread starter Thread starter Michael Kenyon
  • Start date Start date
M

Michael Kenyon

I'm fairly new to Excel, I'm weak with Macro's and I don't know how to
script in VB. That being said my superiors have of course tasked me
with setting up an Excel timesheet. My problem is that Excel doesn't
seem to recognise the 2400 hr or 12:00 AM Time when it calculates
total hours. This is what I'm working with so far:

Time In cell: C11
Time Out cell: D11
Total hrs cell: =HOUR(D11)-HOUR(C11)

This works wonderfully for 3rd and 1st shifts (midnight-8am and
8am-4pm respectively). However, whenever the midnight hour is entered
into D11 (for 2nd shift's end time) I get a total value of -16 hours.
How do I correct this?

The Time In and Time Out cells are formatted for time (Hours:Minutes)
and the Total Hrs cell is formatted for a number value.

Thank you in advance for any help.
 
Michael,

Try this

=IF(HOUR(C11)<=HOUR(D11),HOUR(D11)-HOUR(C11),24+(HOUR(D11)-HOUR(C11)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thank you all very much for all the great suggestions. Ultimately I
went with Biff's solution (I tried them all). Hopefully I won't have
to post too much here again.
 
Back
Top