Browse All Articles > Playing with Time Conversion in Excel
Lot of times in Excel we have to do quick time conversion that is...The time is given in
Time format which is -->12:00:00 and we need to convert it into a number format that is -->12
A quick multipication of same by 24 will convert it to the number format...
so lets say A1-->12:00:00 then in B1-->A1*24 will gives a desired result...Note:- we need to format b1 to Number, By right click over the b1 and format cell.
Similary to find minutes in number format we can do -->A1*24*60 or -->A1*1440 which will give the answer in minutes which is for 12:00:00 is 720 minutes.
In addition to do a vice-versa we can go ahead and divide the same by 24 as in converting number back to time.
Time format which is -->12:00:00 and we need to convert it into a number format that is -->12
A quick multipication of same by 24 will convert it to the number format...
so lets say A1-->12:00:00 then in B1-->A1*24 will gives a desired result...Note:- we need to format b1 to Number, By right click over the b1 and format cell.
Similary to find minutes in number format we can do -->A1*24*60 or -->A1*1440 which will give the answer in minutes which is for 12:00:00 is 720 minutes.
In addition to do a vice-versa we can go ahead and divide the same by 24 as in converting number back to time.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (3)
Commented:
So, what if we put the conversion formula into VBA..and have the instant result into Column A.
I don't know how far my this comment is related and relevant to your article..but I feel you should also include the VBA part with your article to make this complete.
Thank you for your excellent article :)
Author
Commented:Thanks for the comment but to do it with a vba, You will need a code to do tht since with function it will create a cyclic reference. Let me know incase if you are intrested for a vb coding will go ahead and write a code for it.
Saurabh...
Commented:
Time Calculation and Conversion Demonstration:
Part A - Convert time to hours minutes seconds.
Format Column A as TIME, default first option.
In row 1,..... Columns B, C, and D...type: Hours, Minutes, Seconds.
In box A2 ... type this formula: =TIME(TEXT(B2,B2),TEXT(C2,
Fill in the hours minutes and seconds (B2,C2,D2)...that you want the time to represent.
PartB - Convert hours minutes and seconds to time.
In Row 1,.....Columns E, F, G...type: Hours, Minutes, Seconds
In Cell E2, ... type this function: =HOUR(A2)
In Cell F2, ... type this function: =Minute(A2)
In Cell G2, ... type this funciton: =Second(A2)
PartC - Calculate the hours, minutes, and seconds of the current time.
In Cell A3 type this formula: =TIME(HOUR(NOW()),MINUTE(N
Copy Cells E2, F2, and G2.... onto the next Row( E3, F3, and G3.) You can do this by selecting E thru G, then "dragging" it down. Copy until you hit Row 5, for PartD.
Press and hold the F9 key, and watch it count.....
PartD - Calculate how many hours minutes and seconds from Now until 11:59:59 PM...(Midnight)
In Cell A4, ...type: 11:59:59 PM
In Cell A5,....type: =CONCATENATE(TEXT(E5,E5),"
In Cell E5,...type: =SUM(E4-E3)
In Cell F5,...type: =SUM(F4-F3)
In Cell G5,...type: =SUM(G4-G3)
Press and hold the F9 Key, and watch cell A5 as it counts down the hours, minutes, and seconds from the current time until midnight.