| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 30382 | 2003-02-17 21:27:00 | Excel Guru's Help Required | NigelP (863) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 122086 | 2003-02-17 21:27:00 | I have a spreadsheet which is used for working out prices based on hours times $rate. I cannot get it to multiply the $ amount by the total hours and return a sensible answer. For example 71:05 x $20 = 59.236. I know that it has something to do with the format and perhaps I need to convert the time to a decimal, my question is how do I do this? In the above example the 71:05 is formatted as [H]:MM to display a running total of hours from a list above it. Using Excel 2000. | NigelP (863) | ||
| 122087 | 2003-02-17 21:58:00 | Instead of using "Custom" to format the cell use the "Time" Category. I tried it this way, and it worked |
roofus (483) | ||
| 122088 | 2003-02-17 22:44:00 | Thanks, I tried this and it did not work. If cell A1 is 1:05:00 (1h 5 mins formatted as Time) and cell b1 is $20 (formatted as currency) and C1 is =A1*B1 the answer is $0.90 which is not the answer I am looking for. The reason I was using a custom format was I found that the only option under the Time formats had seconds as well and I didn't require that much detail. |
NigelP (863) | ||
| 122089 | 2003-02-17 23:36:00 | Multiply the answer by 24 to get the correct amount. | Russell D (18) | ||
| 122090 | 2003-02-17 23:42:00 | First thing is that in Excel numbers are one thing and formats are another. Formats are the way you as Excel to display the number. In your initial example you try to put time as 71:05, Excel actually reads this as 2.96108 (days). What you are doing is multiplying $20 by the number 2.96108 whereas you (as I understand) actually want to multiply it by the number 71.0833, (which is your hours expressed as a decimal). Excel time formats can be a bit tricky. But without too much further detail, and if you do want to show explicitly your hours and minutes separately you might try the custom (fraction) format # ??/60 You would enter your hours and minutes by hours then space then minutes/60. That is to enter 71 hours 5 minutes, try 71 5/60, in the cell(s) custom formatted as indicated. To see the number this produces, always look in the formula bar when the appropriate cell is selected, not in the cell itself. |
rugila (214) | ||
| 122091 | 2003-02-18 00:20:00 | Just one more addendum on that. If you want to show your time measurement units (hours, minutes etc.) custom format your cells as #"hrs" ??"mins"/60 This should give, or at least it gives me 71hrs 5mins/60 which is about the best I can do without more investigation (ie I don't immediately know how to get rid of the /60 from the display. Ypou can enter your time (using the 71 hours 5 minutes in your example) by either entering 71.0833 or entering 71 space 5 forward slash 60. |
rugila (214) | ||
| 122092 | 2003-02-18 02:44:00 | Assuming the hours are in $A$4 and the $/Hr is in $B$4 =(VALUE(MID(TEXT($A$4,"[h]:mm"),1,LEN(TEXT($A$4,"[h]:mm"))-3))*B4) + ((VALUE(RIGHT(TEXT($A$4,"[h]:mm"),2))*$B$4)/60) You can take out the $ to make it relative if you want but it was easier to work ut this way. Gives 71:05 @ $20/Hr = $1421.67 Hope it helps. |
Big John (551) | ||
| 122093 | 2003-02-18 02:58:00 | Excel stores time as a floating point number, where whole numbers equate to days,and decimals of numbers equate to 1 day (24 hours in hours minutes and seconds). So (as RusselD points out), if you wish to work in hours, you need to multiply x 24. So the simplest answer is your origional equation multipled by 24. This point ( regarding date/time variables) has been discussed several times before in ths forum :| | wuppo (41) | ||
| 122094 | 2003-02-18 03:03:00 | Clarification of above: decimals of numbers equate to less than 1 day. i.e .000 = 0:0:0 and 0.99999999999 etc = 23:59:59. So 71H and 5 Min = 2.96180555555556 as a time type. When formatted as [H]:MM this will show as 71:05 |
wuppo (41) | ||
| 1 | |||||