MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Converting time value to currency value


Posted by Sandana on December 21, 2001 8:32 PM


It looks simple and I'm totally stumped!

Cell A1 has a time value of 18:39:00.
(I've had to use hh:mm:ss so it would calculate past 24 hours) A1 is not an input cell but is calculated from a separate worksheet using the formula
=SUM(Sheet1!I51-"40:00:00"). This gives overtime hours.

My formulas are as follows:

A1 = 18:39:00
B1 = $17.01
C1 =SUM(A1*B1)

This is 18 hours, 39 minutes x $17.01.
C1 should equal $312.81
Instead it gives a value of $13.22

A1 is formatted for time
B1 is formatted for currency
C1 is formatted for currency

Time * currency = currency is what I want but the result is totally wrong.

I don't know how to fix it. Can anyone help?

Thank you very much for any help you may provide.

Sandana


Posted by Jesse Kokane on December 21, 2001 9:05 PM

Covnert 18:39 to hours and minutes by the following:

+HOUR(A1)+ MINUTE(A1)/60

Then multiply this by B1

It looks simple and I'm totally stumped! (I've had to use hh:mm:ss so it would calculate past 24 hours) A1 is not an input cell but is calculated from a separate worksheet using the formula =SUM(Sheet1!I51-"40:00:00"). This gives overtime hours. B1 = $17.01 C1 =SUM(A1*B1) C1 should equal $312.81 Instead it gives a value of $13.22 B1 is formatted for currency C1 is formatted for currency

Posted by Aladin Akyurek on December 22, 2001 12:24 AM

Sandana --

In C1 enter: =A1*24*B1

Aladin

===========

Posted by Sandana on December 22, 2001 9:40 AM

Thank you both so very much! I knew I'd find help here. This board is fabulous. My hat is off to all of you who contribute and take the time to help others. Best wishes and Merry Christmas! : : It looks simple and I'm totally stumped! : Cell A1 has a time value of 18:39:00. : (I've had to use hh:mm:ss so it would calculate past 24 hours) A1 is not an input cell but is calculated from a separate worksheet using the formula : =SUM(Sheet1!I51-"40:00:00"). This gives overtime hours. : My formulas are as follows