Time is my problem!

Andymann · Feb 25th, 2002, 11:54 PM

I am busy doing a time in motion study for one of our sites. I need to convert a decimal value of time, say 6.72 minutes, to the value in hours, minutes and seconds (00:06:43.12). I can't seem to get this right, and because I have worksheets linked, I don't want to type in the separately calculated value. I would appreciate any suggestions!!
Thanks

Andymann

Derek · Feb 26th, 2002, 12:42 AM

Andymann
You could try this
If your decimal minutes is in A1
Put this formula in B1
=A1/60/24
Use this custom format on B1
[h]:mm:ss
Hope this helps
Derek

[ This Message was edited by: Derek on 2002-02-25 23:46 ]

Tom Urtis · Feb 26th, 2002, 12:56 AM

Just to add to Derek's reply, if you enter the formula =A1/1440 in B1 or wherever, and custom format B1 as hh:mm:ss.00, then this will return 00:06:43.20, close to your result but still a little off though, because my .20 decimal remainder is your .12. Where did you get the .12? I must be missing something obvious at this midnight hour.

Tom Urtis

Derek · Feb 26th, 2002, 01:15 AM

You could also put this formula in B1
But to add up the times you would still have to format your cells [h]:mm:ss
=(INT(INT(A1)/60)&":"&MOD(INT(A1),60)&":"&ROUND(MOD(A1,1)*60,0))+0

regards
Derek

Andymann · Feb 26th, 2002, 01:20 AM

Thanks to Derek and Tom for helping out on that one... Much appreciated - the spreadsheet is working properly, and the Boss is a happier person!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 25th, 2002, 11:54 PM	#1
Andymann New Member Join Date: Feb 2002 Posts: 20	I am busy doing a time in motion study for one of our sites. I need to convert a decimal value of time, say 6.72 minutes, to the value in hours, minutes and seconds (00:06:43.12). I can't seem to get this right, and because I have worksheets linked, I don't want to type in the separately calculated value. I would appreciate any suggestions!! Thanks Andymann

Feb 26th, 2002, 12:42 AM	#2
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	Andymann You could try this If your decimal minutes is in A1 Put this formula in B1 =A1/60/24 Use this custom format on B1 [h]:mm:ss Hope this helps Derek [ This Message was edited by: Derek on 2002-02-25 23:46 ]

Feb 26th, 2002, 12:56 AM	#3
Tom Urtis MrExcel MVP Join Date: Feb 2002 Location: San Francisco, California USA Posts: 10,382	Just to add to Derek's reply, if you enter the formula =A1/1440 in B1 or wherever, and custom format B1 as hh:mm:ss.00, then this will return 00:06:43.20, close to your result but still a little off though, because my .20 decimal remainder is your .12. Where did you get the .12? I must be missing something obvious at this midnight hour. Tom Urtis

Feb 26th, 2002, 01:15 AM	#4
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	You could also put this formula in B1 But to add up the times you would still have to format your cells [h]:mm:ss =(INT(INT(A1)/60)&":"&MOD(INT(A1),60)&":"&ROUND(MOD(A1,1)*60,0))+0 regards Derek

Feb 26th, 2002, 01:20 AM	#5
Andymann New Member Join Date: Feb 2002 Posts: 20	Thanks to Derek and Tom for helping out on that one... Much appreciated - the spreadsheet is working properly, and the Boss is a happier person!