Timesheet troubles

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.
 
B

Bob Phillips

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)
 
M

Michael Kenyon

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I get Exel to return # of hrs. worked, start time is enter 1
Converting decimal calculation to h:mm format 3
TIME CALCULATION 5
12 hour shift timesheet calculation 5
Calculate hours, past midnight etc 4
Timesheet in and out rounding 3
Macro / function text time to 24hr excel time passed midnight fortotaling hr's 2
Shift Differential TimeSheet Formulas 8

Top