Time formatting & calculating

[John Smith]

Below is the chain of questions that I still need answers for. I am new
at the time formatting, ascending or descending times, converting time
and how to identify shift start and end so excel can read it. As you
will read I NEED HELP.

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


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


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?

 

[Bob Phillips]

I am not really getting what you want, but I remember the original question.

To change time formatted hours to decimal hours, just multiply by 24. But
honestly, this is unnecessary. Excel can work happily with time, sum it
(just make sure the sum format is [h]:mm), sort it, etc., so I wouldn't
bother.

To do your calculations, you need 4 pieces of information, the time they
actually start and they actually end, and their shift start and end times.
In the formulae you quote, F1 is the actual end time, E1 the actual start
time. shift_start and shift_end are logical references to the other two
times that you need somewhere on the worksheet.

Not clear what you mean by ... how do I let the little wizard inside know
what that time is ...

--
HTH

Bob Phillips

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

 

[John Smith]

I am not really getting what you want, but I remember the original question.

To change time formatted hours to decimal hours, just multiply by 24. But
honestly, this is unnecessary. Excel can work happily with time, sum it
(just make sure the sum format is [h]:mm), sort it, etc., so I wouldn't
bother.

To do your calculations, you need 4 pieces of information, the time they
actually start and they actually end, and their shift start and end times.
In the formulae you quote, F1 is the actual end time, E1 the actual start
time. shift_start and shift_end are logical references to the other two
times that you need somewhere on the worksheet.

Not clear what you mean by ... how do I let the little wizard inside know
what that time is ...

Thanks Again Bob,
The wizard reference is that I don't know how to tell excel how to set
up and reference the start, stop shift times. I will try to work with
this and see if I can understand it.
John

 

[John Smith]

I am not really getting what you want, but I remember the original question.

To change time formatted hours to decimal hours, just multiply by 24. But
honestly, this is unnecessary. Excel can work happily with time, sum it
(just make sure the sum format is [h]:mm), sort it, etc., so I wouldn't
bother.

To do your calculations, you need 4 pieces of information, the time they
actually start and they actually end, and their shift start and end times.
In the formulae you quote, F1 is the actual end time, E1 the actual start
time. shift_start and shift_end are logical references to the other two
times that you need somewhere on the worksheet.

Not clear what you mean by ... how do I let the little wizard inside know
what that time is ...

I am now convinced that I am incapable of explaining what I need this to
do or totally too dense to grasp the explanation. I still can't get
this to give me the number of hours worked on a holiday during or
outside the regular shift to indicate which reimbursement is earned. I
have a cell for the start, end, shift start and shift end times. Given
these in the formula that you provided
If(AND(C1>=shift_start,D1<=shift_end),"C","H" I get an error message
#name. If I enter the shift start and end times in separate cells and
use these as reference in the formula, I still get the same error. I
know that I am close but just can't get over the hump. I do not know
how to enter the references or have something formatted incorrectly. I
have it calculating the lapsed time in hours (8:00) but can not get it
to convert to number of hours (8.0).

 

[nofliesonyou]

To convert "time" in hours (8:00) to a number of hours (8.0), create
another column next to the "time" and multiply by 24. FORMAT then new
"hours" to general or decimal, Excel thinks its smart and will
autoformat your new column to time.

I am not really getting what you want, but I remember the original question.

To change time formatted hours to decimal hours, just multiply by 24. But
honestly, this is unnecessary. Excel can work happily with time, sum it
(just make sure the sum format is [h]:mm), sort it, etc., so I wouldn't
bother.

To do your calculations, you need 4 pieces of information, the time they
actually start and they actually end, and their shift start and end times.
In the formulae you quote, F1 is the actual end time, E1 the actual start
time. shift_start and shift_end are logical references to the other two
times that you need somewhere on the worksheet.

Not clear what you mean by ... how do I let the little wizard inside know
what that time is ...

I am now convinced that I am incapable of explaining what I need this to
do or totally too dense to grasp the explanation. I still can't get
this to give me the number of hours worked on a holiday during or
outside the regular shift to indicate which reimbursement is earned. I
have a cell for the start, end, shift start and shift end times. Given
these in the formula that you provided
If(AND(C1>=shift_start,D1<=shift_end),"C","H" I get an error message
#name. If I enter the shift start and end times in separate cells and
use these as reference in the formula, I still get the same error. I
know that I am close but just can't get over the hump. I do not know
how to enter the references or have something formatted incorrectly. I
have it calculating the lapsed time in hours (8:00) but can not get it
to convert to number of hours (8.0).