Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 17
Default 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-


  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,440
Default 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-
|
|


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 17
Default 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-
|
|




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 17
Default 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-
|
|






  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,440
Default 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-
| |
| |
|
|
|
|
|
|


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mileage Claim Formula johndavies New Users to Excel 4 August 14th 06 09:24 AM
multiply total hours by hourly pay charitydc Excel Discussion (Misc queries) 3 June 22nd 06 07:15 PM
Hourly rate times hours? L Excel Worksheet Functions 1 May 8th 06 06:27 PM
Pivot table for reporting sales performance Ram Excel Discussion (Misc queries) 2 February 6th 06 11:06 AM
how to calculate total months from month/year patti Excel Discussion (Misc queries) 1 December 8th 04 10:50 PM


All times are GMT +1. The time now is 10:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"