Playing with Time Conversion in Excel

Saurabh Singh Teotia
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.
Saurabh Singh Teotia

Comments (3)


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


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.

Ron MalmsteadSr. Developer

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.

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.