PDA

View Full Version : Sleeper: Converting Hrs:Mins to general/currency value



reynard
05-25-2005, 01:48 AM
I've started a spreadsheet which logs workers shift patterns shown in rotary (24 Hr) shifts.
What I'm trying to do is to convert the hours/mins to either a number or currency value.
An example is as follows :-
Start Finish Hrs Cost ?
6:00 14:00 8:00

6:00:00 AM 2:00:00 PM =SUM(AJ14-AI14) ??????

Any hel pwould be gratefully received !
Regards,
Reynard

Bob Phillips
05-25-2005, 02:06 AM
I've started a spreadsheet which logs workers shift patterns shown in rotary (24 Hr) shifts.
What I'm trying to do is to convert the hours/mins to either a number or currency value.
An example is as follows :-
Start Finish Hrs Cost ?
6:00 14:00 8:00

6:00:00 AM 2:00:00 PM =SUM(AJ14-AI14) ??????

I am not really sure which part you want help on, so I'll try and cover all bases.

Firtsly, to get the time worked, you just subtract the start tiome from the end time. So say in C2


=B2-A2

This gives you total time worked, as time. Time is held in Excel as a fraction of 1 day, so for instance 8 hours is .333333, 12 hours is .5 etc. To convert that to dec imal hours you multiply by 24. So say in D2


=C2*24

You now have the hours worked in an easily workable form, so you just multiply by the rate, for instance with a rate of $70 per hour


=D2*70

which you format as currency.

It can all be done in one step


=(C2-B2)*24*70