how can I multiply number of hours and minutes by hourly rate?

[Guest]

I have set-up a worksheet that allows me to track start and stop times for
each client so I can have total billable hours for each client at the end of
the month. This works fine but I would like to take it one step further and
put in an hourly rate for each client and let Excel calcuate the amount to
invoice at the end of the month.

The problem I am having is that I can't seem to separate the hours from the
minutes to do the calculation properly. Right now I hve a single cell
formatted with the total monthly hours and minutes. Often this number is
greater than 24 hours (ie 35:28). I want to multiply the total number of
hours and minutes by a dollar value and come up with a total dollar value for
the month.

The MINUTE function seems to take care of the minutes fine. However the HOUR
function divides by 24 and returns only a number less than 24. This is not
acceptable for billable hours greater than 24 per month.

Any ideas about how to get the right total?

 

[Niek Otten]

Times in Excel are stored as fractions of a day.
So Time*24*Rate gives your required value

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I have set-up a worksheet that allows me to track start and stop times for
| each client so I can have total billable hours for each client at the end of
| the month. This works fine but I would like to take it one step further and
| put in an hourly rate for each client and let Excel calcuate the amount to
| invoice at the end of the month.
|
| The problem I am having is that I can't seem to separate the hours from the
| minutes to do the calculation properly. Right now I hve a single cell
| formatted with the total monthly hours and minutes. Often this number is
| greater than 24 hours (ie 35:28). I want to multiply the total number of
| hours and minutes by a dollar value and come up with a total dollar value for
| the month.
|
| The MINUTE function seems to take care of the minutes fine. However the HOUR
| function divides by 24 and returns only a number less than 24. This is not
| acceptable for billable hours greater than 24 per month.
|
| Any ideas about how to get the right total?

 

[Guest]

Hello Niek,

Thanks for your answer but I'm afraid that I don't understand.

The TIME function seems to require fields of hour, minute, second. My
monthly totals are in a single cell formatted as HH:MM. The problem that I am
having is separating off the hours if they are greater than 24. For example,
If I have a cell of 35:28 what function do I need to use get the hours and
minutes as a decimal (35.467)?

Regards,

Chris

 

[Niek Otten]

Hi Chris,

Just multiply by 24 and format as General.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hello Niek,
|
| Thanks for your answer but I'm afraid that I don't understand.
|
| The TIME function seems to require fields of hour, minute, second. My
| monthly totals are in a single cell formatted as HH:MM. The problem that I am
| having is separating off the hours if they are greater than 24. For example,
| If I have a cell of 35:28 what function do I need to use get the hours and
| minutes as a decimal (35.467)?
|
| Regards,
|
| Chris
|
| "Niek Otten" wrote:
|
| > Times in Excel are stored as fractions of a day.
| > So Time*24*Rate gives your required value
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| >
| > |I have set-up a worksheet that allows me to track start and stop times for
| > | each client so I can have total billable hours for each client at the end of
| > | the month. This works fine but I would like to take it one step further and
| > | put in an hourly rate for each client and let Excel calcuate the amount to
| > | invoice at the end of the month.
| > |
| > | The problem I am having is that I can't seem to separate the hours from the
| > | minutes to do the calculation properly. Right now I hve a single cell
| > | formatted with the total monthly hours and minutes. Often this number is
| > | greater than 24 hours (ie 35:28). I want to multiply the total number of
| > | hours and minutes by a dollar value and come up with a total dollar value for
| > | the month.
| > |
| > | The MINUTE function seems to take care of the minutes fine. However the HOUR
| > | function divides by 24 and returns only a number less than 24. This is not
| > | acceptable for billable hours greater than 24 per month.
| > |
| > | Any ideas about how to get the right total?
| >
| >
| >

 

[Guest]

If your cells are actually formatted as a time value you just have to
multiply by 24 and format as a number. For example say your time value
(35:28) is located in A1, in B1 put =A1*24, and format B1 as a "general" or
"number"

 

[Guest]

It was the "format as general" that was the key. Works perfectly. Thanks for
your help.

Chris