Holiday Time Calculation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Smith - 14 Jul 2006 12:25 GMT

Our employees that are called for emergency work on designated holidays
earn two types of reimbursable time. If they work during what would
have been their normal shift they receive "H" leave for those hours. If
they work outside (whether called in prior or extend beyond or totally
outside)what would have been their normal shift, they earn "C" leave. I
have set up the excel sheet to have their start time and finish time in
separate columns. Column c is start and d is end. I have formatted
their times in the 24 hour mode (hh:mm). I have worked with the If
feature and the If(and feature but can not get a formula to analyze the
start and finish columns simultaneously to determine the H or c leave.
And, is there a format or formula that will analyze the times and give
me a total hours worked (that's why I used the 24 hour format but it has
not worked out for me yet)? Any direction will be appreciated.
John

Reply to this Message

Bob Phillips - 14 Jul 2006 13:08 GMT

Just use a formula like

=F1-E1+(E1>F1)

to calculate elapsed time.

To check if H or C, use

=IF(AND(E1>=shift_start,F1<=shift_end),"H","C")

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Our employees that are called for emergency work on designated holidays
> earn two types of reimbursable time. If they work during what would
[quoted text clipped - 10 lines]
> not worked out for me yet)? Any direction will be appreciated.
> John

Reply to this Message

John Smith - 15 Jul 2006 01:01 GMT

> Just use a formula like
>
[quoted text clipped - 5 lines]
>
> =IF(AND(E1>=shift_start,F1<=shift_end),"H","C")

Thank you for your help. I have tried both and they work. Now to share
more of my ignorance: is there a way to have the amount of hours worked
(answer in first formula above) convert to number of hours (example,
2.5, 7.5, 3.0) and the like. What I did not say in the first cry for
help is that column G is for the amount of hours of H and column H is
for the amount of C hours. So basically, how can I convert the lapsed
time into the number of hours and is there a way to analyze the start
and stop times and sort them into the respective columns (as above) as
the numbers of hours for each just by giving the start and stop times.
Also, in the formula you used shift_start and shift_end; how do I let
the little wizard inside know what that time is?
As you can see, I am in need of help.

Reply to this Message

Rate this thread: