Excel Time Calculation
Thread Starter
Join Date: Feb 2003
Location: Io
Posts: 420
Likes: 0
Received 0 Likes
on
0 Posts
Excel Time Calculation
I need to divide an amount of hours, minutes and seconds by a value to get a per minute rate. I can't get it to work. I'm a complete thicko with MS Excel. Can anyone help?
Thanks in advance.
MF
Thanks in advance.
MF
(a bear of little brain)
Join Date: Aug 2001
Location: 51 10 03.70N 2 58 37.15W
Age: 75
Posts: 273
Likes: 0
Received 0 Likes
on
0 Posts
Format the cells with the cells (say column a) as a number and the cells with the time as 'hh:mm'. So we then have, say, column A formatted as num, 2 dec, and column b as hh:mm. In column c row 1 (or wherever) use the formula '=A1/(B1*1440)'.
(Results then would be, A1 = '40.00' (i.e. £40), B1= '1:30' (one and a half hours) C1 would then be '0.44' (i.e. 44p per minute).
(Results then would be, A1 = '40.00' (i.e. £40), B1= '1:30' (one and a half hours) C1 would then be '0.44' (i.e. 44p per minute).
Self Loathing Froggy
Join Date: Jun 2002
Location: elsewhere
Age: 18
Posts: 546
Likes: 0
Received 0 Likes
on
0 Posts
MadsDad's suggestion is indeed quite right.
For pedagogy's sake, I would just like to explain why :
The unit time in Excel is the day (whichever format you use to display it), hence the 1440 factor = 24 hours x 60 minutes.
For pedagogy's sake, I would just like to explain why :
The unit time in Excel is the day (whichever format you use to display it), hence the 1440 factor = 24 hours x 60 minutes.
(a bear of little brain)
Join Date: Aug 2001
Location: 51 10 03.70N 2 58 37.15W
Age: 75
Posts: 273
Likes: 0
Received 0 Likes
on
0 Posts
Bre I should have put some comment about the 1440, didn't think to. Also the fact that the 'hh:mm' restricts the time to hours and minutes.
And, of course, if anyone wasnts to work out the cost/second the time format would need to be 'hh:mm:ss' (so now you've got seconds as well) and the multiplication would need to be (pause to use calculator) '*86400'.
(And if you want it per day, leave off the '*1440').
And, of course, if anyone wasnts to work out the cost/second the time format would need to be 'hh:mm:ss' (so now you've got seconds as well) and the multiplication would need to be (pause to use calculator) '*86400'.
(And if you want it per day, leave off the '*1440').