dollar amount versus Time

W

Webitect

Hello,

Could anyone help me out with this? I have two columns, one calculates the
amount of time spent from a "time in" column and a "time out" column. The
other column calculates the amount of money made (or lost) from a starting
balance to an ending balance. I am trying to get another column to
calculate $/Hr. I can't get it to work for some reason. Also, when I try
to add up the total amount of time in the "total time" column, it'll only
add a few cells correctly...anymore than 5 cells it won't report anything.

Any advice would be greatly appreciated.

Sincerely,

Ray
 
N

Niek Otten

Hi Ray,

=A1*A2*24 (since times are stored as fractions of a day so 1 means 24 hrs
and 1 hr is stored as 1/24th.
To add times, format the total as Custom, [h]:mm. This avoids "topping over"
at 24 hrs.
 
P

Peo Sjoblom

You need to make the time into decimal time, e.g.

Assume you have 12:30 in A1, since one excel hour is 1/24 (1 = one day in
excel) so you need to multiply the time with 24 first, same as if you want a
total of hours multiplied by a payrate

=A1*24

or

=amount/(A1*24)

or

=round(amount/(A1*24),2)

rounded to the nearest cent

note that you must format the result as general, number or currency or else
Excel tries to be helpful and sensing a time value will format result as
time (stupid really)

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon
 
Top