Playing with Time Conversion in Excel

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

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.