How to calculate total cost using fixed hourly rate from [h:mm:ss:]?
 

Hi there,

I have a spreadsheet keeping track of time I spend on a project.

The Total Time cell is formatted as Special and specified as [h:mm:ss:]

I have a standard hourly rate and I would like to calculate the total time
spent & display the result in Pounds Sterling - the cell is already
formatted as Currency

For example the Total Time is 10h:01mm:41ss: and I want to multiply this by
the Hourly rate which is 30 Pounds Sterling.

What is the formula to achieve this please?


--
Thanks & kind regards,
-pp-

How to calculate total cost using fixed hourly rate from [h:mm:ss:]?
 

If the time is in A1:

=A1*24*30

Time is stored as a faction of a day so *24 gives you the number of hours, including a decimal part for minutes and seconds

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Pheasant Plucker®" wrote in message ...
| Hi there,
|
| I have a spreadsheet keeping track of time I spend on a project.
|
| The Total Time cell is formatted as Special and specified as [h:mm:ss:]
|
| I have a standard hourly rate and I would like to calculate the total time
| spent & display the result in Pounds Sterling - the cell is already
| formatted as Currency
|
| For example the Total Time is 10h:01mm:41ss: and I want to multiply this by
| the Hourly rate which is 30 Pounds Sterling.
|
| What is the formula to achieve this please?
|
|
| --
| Thanks & kind regards,
| -pp-
|
|

How to calculate total cost using fixed hourly rate from [h:mm:ss:]?
 

Thank you very much Nick, I wasn't sure how the time was stored...

Quick response and just the job!

Kind regards,
-=pp=-


"Niek Otten" wrote in message
...
If the time is in A1:

=A1*24*30

Time is stored as a faction of a day so *24 gives you the number of hours,
including a decimal part for minutes and seconds

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Pheasant Plucker®" wrote in message
...
| Hi there,
|
| I have a spreadsheet keeping track of time I spend on a project.
|
| The Total Time cell is formatted as Special and specified as [h:mm:ss:]
|
| I have a standard hourly rate and I would like to calculate the total
time
| spent & display the result in Pounds Sterling - the cell is already
| formatted as Currency
|
| For example the Total Time is 10h:01mm:41ss: and I want to multiply this
by
| the Hourly rate which is 30 Pounds Sterling.
|
| What is the formula to achieve this please?
|
|
| --
| Thanks & kind regards,
| -pp-
|
|

How to calculate total cost using fixed hourly rate from [h:mm:ss:]?
 

Sorry about this...follow-on question if I may please...

I need to display a new result in C41 which will be the sum of C31
(currently displaying 10:23:26 and then subtract 6:30:00 from this and
display the new result in C41

C31 is likely to change as I add more time worked but the 6 hours 30 minutes
which I need to subtract will stay constant.

I have tried a few ideas none of which have worked.

Please excuse my ignorance but am I missing something fundamental here?

Thanks & regards,
-=pp=-

"Pheasant Plucker®" wrote in message
...
Thank you very much Nick, I wasn't sure how the time was stored...

Quick response and just the job!

Kind regards,
-=pp=-


"Niek Otten" wrote in message
...
If the time is in A1:

=A1*24*30

Time is stored as a faction of a day so *24 gives you the number of
hours,
including a decimal part for minutes and seconds

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Pheasant Plucker®" wrote in message
...
| Hi there,
|
| I have a spreadsheet keeping track of time I spend on a project.
|
| The Total Time cell is formatted as Special and specified as
[h:mm:ss:]
|
| I have a standard hourly rate and I would like to calculate the total
time
| spent & display the result in Pounds Sterling - the cell is already
| formatted as Currency
|
| For example the Total Time is 10h:01mm:41ss: and I want to multiply
this
by
| the Hourly rate which is 30 Pounds Sterling.
|
| What is the formula to achieve this please?
|
|
| --
| Thanks & kind regards,
| -pp-
|
|

How to calculate total cost using fixed hourly rate from [h:mm:ss:]?
 

Put 6:30:00 in a cell, let's say A1.
In C42:
=C31-A1.
If C31 can be less than 6:30:00:
=MAX(0,C31-A1)

If you are likely to go over 24 hours, format custom as [h]:mm:ss to prevent tipping over at 24h.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Pheasant Plucker®" wrote in message ...
| Sorry about this...follow-on question if I may please...
|
| I need to display a new result in C41 which will be the sum of C31
| (currently displaying 10:23:26 and then subtract 6:30:00 from this and
| display the new result in C41
|
| C31 is likely to change as I add more time worked but the 6 hours 30 minutes
| which I need to subtract will stay constant.
|
| I have tried a few ideas none of which have worked.
|
| Please excuse my ignorance but am I missing something fundamental here?
|
| Thanks & regards,
| -=pp=-
|
| "Pheasant Plucker®" wrote in message
| ...
| Thank you very much Nick, I wasn't sure how the time was stored...
|
| Quick response and just the job!
|
| Kind regards,
| -=pp=-
|
|
| "Niek Otten" wrote in message
| ...
| If the time is in A1:
|
| =A1*24*30
|
| Time is stored as a faction of a day so *24 gives you the number of
| hours,
| including a decimal part for minutes and seconds
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "Pheasant Plucker®" wrote in message
| ...
| | Hi there,
| |
| | I have a spreadsheet keeping track of time I spend on a project.
| |
| | The Total Time cell is formatted as Special and specified as
| [h:mm:ss:]
| |
| | I have a standard hourly rate and I would like to calculate the total
| time
| | spent & display the result in Pounds Sterling - the cell is already
| | formatted as Currency
| |
| | For example the Total Time is 10h:01mm:41ss: and I want to multiply
| this
| by
| | the Hourly rate which is 30 Pounds Sterling.
| |
| | What is the formula to achieve this please?
| |
| |
| | --
| | Thanks & kind regards,
| | -pp-
| |
| |
|
|
|
|
|
|