vlookup and calculation questions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dtmd - 18 Nov 2005 21:36 GMT

I am working to re-design the emergency timesheets for those deployed for
Hurricane duty. They work 12 hour days. Each location gives a Tour of Duty
which will determine how overtime is broken down. I have scheduled overtime,
unschedule overtime, and overtime with night differential as well as the
regular 8 hour day. I need to set something up that will allow them to input
their set TOD (i.e. 0600-1830) and then the clock hours they work (checking
in and out for lunch) and be able to break out the hours from there in the
appropriate columns. For instance - if a TOD is 0600-1830 - the hours would
break out as follows for M-F:

0600-1200 - 6 RG
1230-1530 - 3 RG
1530-1800 - 3.5 OS
1800-1830 - .5 OS/ND

Now lets say they decided to work until 2100 that night. I would have to
then add:
1830-2100 2.5 OU

So - the TOD determines the break out of the OT hours, but the clock hours
determine the amounts and if any Unscheduled Overtime is necessary.

How would I set this up?

Reply to this Message

David McRitchie - 19 Nov 2005 21:56 GMT

Hi ...,
The formula for the difference between start and stop
=C2-B2+(B2>C2)
The part in parens is a logical expression returning 0 or 1
-- 1 day is 24 hours so if B2 is greater than C2 then 24 hours are added.

If you have a time in hours and minutes shown as Excel time -- h:mm
and want to convert that to a decimal number with a decimal fraction multiply Excel time by 24.

For time entry without the colons see Chip Pearson's page:
Dates Quick Entry: http://www.cpearson.com/excel/DateTimeEntry.htm

For an example of a time sheet see (also see John Walkenbach's example)
Working With Overtime Hours In Excel : http://www.cpearson.com/excel/overtime.htm

Anything you want to know about date and time can probably be found in
http://www.cpearson.com/excel/datetime.htm
http://www.mvps.org/dmcritchie/excel/datetime.htm

And I'm sure it does not apply to your question but VLOOKUP is covered in
http://www.mvps.org/dmcritchie/excel/vlookup.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

> I am working to re-design the emergency timesheets for those deployed for
> Hurricane duty. They work 12 hour days. Each location gives a Tour of Duty
[quoted text clipped - 19 lines]
>
> How would I set this up?

Reply to this Message

David McRitchie - 19 Nov 2005 22:34 GMT

One think I left out, was if you were to total time as hh:mm down
a column you would want to format the totals as [h]:mm to keep the
hours from overflowing into days.

Reply to this Message