Playing with Time Conversion in Excel

AID: 183

Status: Published

5500 points

Bysaurabh726
TypeGeneral
Posted on2008-11-20 at 16:03:21

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.

Asked On: 2008-11-20 at 16:03:21ID183
Tags: Excel Time Conversion
Topic: Microsoft Excel Spreadsheet Software
Views: 10212

About The Author

saurabh726

Comments

Expert Comment

by: suvmitra on 2009-06-18 at 11:29:58ID: 1694

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 :)

Author Comment

by: saurabh726 on 2009-06-18 at 12:55:18ID: 1698

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...

Expert Comment

by: xuserx2000 on 2009-06-19 at 14:23:52ID: 1718

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.

Add your Comment

Please Sign up or Log in to comment on this article.

Gain Access to all our Tech Resources

Get personalized answers

Ask unlimited questions

Access Proven Solutions

Search 3.2 million solutions

Read In-Depth How-To Guides

1000+ articles, demos, & tips

Watch Step by Step Tutorials

Learn direct from top tech pros

And Much More!

Your complete tech resource

30-day free trial. Register in 60 seconds.