We don't really have enough information. hh:mm:ss is actually a TIME OF DAY display format.
As you probably already know, Excel stores date and time as a number. the date is the number of days from 1/1/1900 (so 31-Aug-2012 is 41152) "time" is actually the fractional part of a day. at 6:00AM, 1/4 of the day is complete, so the "time" is .50000 at 10:12:40 am, .4254662037 of the day has passed. So, if the total number of hours between two date/time(s) is less than 1 day (say from 30-Aug-2012 12:12:40 PM to 31-Aug-2012 10:12:00 AM) then you can simply subtract the two numbers = 0.916203703709471 Changing the display format to hh:mm:ss will show you 21:59:20 however, it's important to note that what you're REALLY doing is displaying the date 01-Jan-1900 9:59:20 PM it just LOOKS like what you expect. But if the difference is MORE than 24 hours, it will only show you the decimal portion of the result (the fractional part of the day). because, after all, there's no such thing as 26 o'clock! So.. what you're going to have to do is mathematically calculate the number of hours by taking int(timeval) * 24 to get the number of hours. =INT($A$3*24) To get the number of minutes, you need to get the total minutes by multiplying the time interval by 24 to get hours, then 60 to get minutes. then, if you use mod() to basically divide it by the number of minutes in the hours of full hours (from above) and return the minutes that were less than 60: =INT(MOD($A$3*24*60,INT($A$3*24))) Using the same technique, you calculate the total seconds, then use MOD() to remove the seconds from full minutes: =INT(MOD($A$3*24*60*60,INT($A$3*24*60))) So.. you can put this all into a cell like: =INT($A$3*24) & " hrs, " & INT(MOD($A$3*24*60,INT($A$3*24))) & " mins, " & INT(MOD($A$3*24*60*60,INT($A$3*24*60))) & " secs" or, if you really want it in hh:mm:ss =INT($A$3*24) & ":" & INT(MOD($A$3*24*60,INT($A$3*24))) & ":" & INT(MOD($A$3*24*60*60,INT($A$3*24*60))) actually, that's h:m:s If you really want leading 0's, you'll have to use a =TEXT() function, like: =TEXT(INT($A$3*24),"00") & ":" & TEXT(INT(MOD($A$3*24*60,INT($A$3*24))),"00") & ":" & TEXT(INT(MOD($A$3*24*60*60,INT($A$3*24*60))),"00") Paul ----------------------------------------- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley ----------------------------------------- ________________________________ From: Chandra Shekar <chandrashekarb....@gmail.com> To: excel-macros@googlegroups.com Sent: Fri, August 31, 2012 7:59:43 AM Subject: $$Excel-Macros$$ Number into hr:mm;ss Hello, How to convert numbers into day hh:mm:ss Format based on any two dates. Thanks in advance. Regards, Chandru -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
