Time / Currency in Excel 2000

[UkMister]

Hi,

Just a quick question - Is it possible to perform calculations on date and currency in Excel 2000?

What I would like to do is calculate an hourly rate in £s when given a start time, a finish time and amount earned during that period.

For example - Start at 8pm Finish at 10pm and earn £10. The Hourly rate would be £5. Easy for me to do but would like to automate the process if possible.

Many thanks

Gary

 

[SuicidED]

This might work =C1/(HOUR(B1)-HOUR(A1))

 

[combo]

If you have currency or date/time values - they are in fact formatted regular numbers, so you can use them to perform all arithmetic operations. Sometimes the result is confusing as excel automatically formats formula cell using one of argument formats.
For date/time values, one day is 1, one hour =1/24, etc. You cannot display negative time, however you have proper negative number behind it.

combo

 

[UkMister]

Hi, no this didn't work. It might be me though.

c1 (the amount earned)
b1 (start time)
a1 (end time)

The cells are in their proper formats (time&currency). Does this make a difference?

Cheers

 

[SuicidED]

Ok try this going on the fields you have above
=ABS(C1/(HOUR(B1)-HOUR(A1)))

 

[combo]

=C1/(B1-A1)/24 will give the rate. Just format the result as number. If this does not work - check if the input data are numbers. Format them as general, as the result you should see numbers without any additional characters.

combo

 

[UkMister]

Looks like a difficult one

I have found this formula (from exceltips.com):-

=G3-E3+IF(E3>G3,1)

where : g3 is finishing time
e3 is start time

This gives the amount of time worked even if the start/finish time are different days. (eg 22:00 - 01:00)

The result is given in hours/minutes. This just leaves me with the problem of dividing an amount in money by an amount in time to give a total in money

 

[UkMister]

Hi,

Thanks for all your help. For anyone who wants to know, I eventually got the hourly rate using this formula

=(I3/(H3*24))

where I3 is money earned (£)
H3 is hours worked (hh:mm)

Once again, thanks for your help

Gary