| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 50787 | 2004-11-01 19:42:00 | excel time function | Trev O (452) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 286859 | 2004-11-01 19:42:00 | Does anyone know if I can add the time that I work into a spreadsheet in hours and minuits (1.30 =1hr 30mins) and some formula or function converts it into numbers (say 1hr=$30 so the result would be '45') ? The output numbers can be added up with the sum function at the end of the column (for the days total) and end of row too (for the clients total), leaving just the hrs & mins in the middle of the sheet... any help much appriciated thanks Trev |
Trev O (452) | ||
| 286860 | 2004-11-01 20:04:00 | Easiest to use one column for hours, and another for minutes. If A2=hours, B2=Minutes and C2 =the resulting $ then: C2 formula is =30*((A2)+(B2/60)) |
godfather (25) | ||
| 286861 | 2004-11-01 20:31:00 | Hi, If you type into cell A1: 1:30, Excel will recognise this as 1 hour and 30 minutes. Excel understands the value of this cell to be a fraction of 24 hours, so in Cell A2 if you type =A1*24 the result will be 1.5. You can either change the formula in cell A2 to = A1*24*(Hourly Rate), or set it up any heaps of other ways. Hope this helps. :-) |
Marlboro (4607) | ||
| 286862 | 2004-11-01 22:25:00 | Thanks guys, I'll have a proper play with that later Marlboro, but that looks great... I was hoping to steer clear of too many boxes GF but thanks for the input, I may put more boxes in now anyway though, one with the time in and the other with the price in! looking at how easy it seems. Trev |
Trev O (452) | ||
| 286863 | 2004-11-02 08:35:00 | That's great Marlboro, it seems so easy to miss all these different functions in Excel Do you know if there is there any way of getting rid of the am/pm thing, as it always comes up as 00:00:00am instead of just 00:00 It not too difficult if I do over an hour, but if I put in 0:20 (for 20mins) it goes to 12:20:00am which is a it cumbersome. thanks for your help Trev |
Trev O (452) | ||
| 286864 | 2004-11-02 11:50:00 | Format the cell to use hh:mm | Big John (551) | ||
| 286865 | 2004-11-02 19:22:00 | The cells seem to already be set to hh:mm bigjohn.... if I put it to hh:mm am/pm it displays the am/pm bit. any other ideas? |
Trev O (452) | ||
| 286866 | 2004-11-02 21:33:00 | The key is in the format of the cells . Make sure that you have not done any special formatting to the column, i . e . set the format to "General" . When I type in 00:20 into a "General" formatted cell, it appears as 00:20, however if you change the format to the date time format you may get the AM / PM thing Cheers |
Marlboro (4607) | ||
| 286867 | 2004-11-03 00:33:00 | I usually set my format to custom and use hh:mm and it works 100% of the time. If you dont want the leading 0 then use h:mm If you use the date/time format then you may get the am/pm setting as well. |
Big John (551) | ||
| 1 | |||||