Playing with Time Conversion in Excel

Published:
Updated:
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.

3
15,357 Views

Manager

Commented:
This is an excellent article. However, I have a feeling, people do not want to dedicate extra columns therefore they will try to have the instant result / conversion in a single column.

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 :)
CERTIFIED EXPERT
Top Expert 2015

Commented:
Hi Suvmitra,

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...
Sr. Developer
CERTIFIED EXPERT

Commented:
There are built-in conversion functions in Excel.

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,C2),TEXT(D2,D2))
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(NOW()),SECOND(NOW()))
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),":",TEXT(F5,F5),":",TEXT(G5,G5))
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.