<

Playing with Time Conversion in Excel

Published on
18,310 Points
15,010 Views
3 Endorsements
Last Modified:
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
Comment
3 Comments
LVL 9

Expert Comment

by:suvmitra
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 :)
0
LVL 59

Author Comment

by:Saurabh Singh Teotia
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...
0
LVL 25

Expert Comment

by:Ron Malmstead
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.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Join & Write a Comment

This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month