Multiplying Time Values (eg. hh:mm) by currency (eg $20 perhour)

[Guest]

Hi, I currently have a spreadsheet to workout the number of hours worked from start time to finish tim
EG. Start 06:00 Finish 13:00 and value evaluates to time value of 6:50 meaning 6 1/2 hrs worked

I now wish to multiple the value 6:50 by a currency value (eg. $20 per hour) to make a new currency value but can only recieve a value of $5.00

Any suggestions/idea would be appreciate

Brad

 

[wpburkhimer1]

A couple of suggestions...

1. Make sure you're using the right format for your time fields. I would
have expected that starting at 06:30 a.m. and finishing at 1:00 p.m. on the
same day would result in 6:30, or 6 hours and 30 minutes worked. I haven't
studied all the possible time formats, but I'm betting that 6:50 is supposed
to mean 10 minutes before 7 o'clock, not 6 thirty.

2. From some quick experimentation, it seems to me that when a time value
is used in an equation of the sort you speak of, it resolves to the time in
fraction of a day. Thus, if your rate is an hourly rate, try multiplying
your answer by 24 hours per day and see if the answer doesn't come out
right.

HTH

--Pete

Hi, I currently have a spreadsheet to workout the number of hours worked from start time to finish time
EG. Start 06:00 Finish 13:00 and value evaluates to time value of 6:50 meaning 6 1/2 hrs worked.

I now wish to multiple the value 6:50 by a currency value (eg. $20 per

hour) to make a new currency value but can only recieve a value of $5.00.

 

[Norman Harker]

Hi Brad!

A good time resource is:

Chip Pearson:
http://www.cpearson.com/excel/datetime.htm#AddingTimes

Generally if you enter times and it is formatted as time 6:50 can be
looked at as representing a time of 10 to 7 or an elapsed time of 6
hours 50 minutes. But this is only how it is displayed; that's what
formatting is all about. The time is stored in the computer as a
decimal part of 1 day and to use that time in hourly rate calculations
you need to multiply by 24.

Example
A1:
7:30
B1:
14:45
C1:
=B1-A1
Returns: 7:15 [Which is 7 hours 15 minutes elapsed time]
D1:
=C1*24*12
Returns: $87.00
Note that D1 is formatted as currency

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Hi, I currently have a spreadsheet to workout the number of hours

worked from start time to finish time

EG. Start 06:00 Finish 13:00 and value evaluates to time value of 6:50 meaning 6 1/2 hrs worked.

I now wish to multiple the value 6:50 by a currency value (eg. $20

per hour) to make a new currency value but can only recieve a value of
$5.00.

 

[Mike]

A1: 06:30 (Format hh:mm)(Start)
B1: 13:00 (Format hh:mm)(Finish)
C1: 00:30 (Format hh:mm)(Lunch time)
D1: Formula =B1-A1-C1
Display 6:30 (Format hh:mm)
E1: 20 (Format General)
F1: Formula =E1*D1/"1:0"
Display 130 (Format General)