How to derive time from the value consisting date time [SOLVED]

[ThrottleWorks]

Hi,


I have 2 Columns, AJ & AP.


Value in AJ2 = 10/10/2010 1:40:24 AM


I want value as 1:40:24 AM in cell AP2.


The data is in thousands of rows, so I will run a loop for populating the value.


Can anyone please help about how to derive only time value from the cell.

 

[ThrottleWorks]

Hi,


I got the solution, I will use "=(AJ2-INT(AJ2))".


I do not know how it works, copied from


http://excelsemipro.com/2012/10/extract-time-from-a-date-time-number-in-excel/


Thanks.

 

[bobhc]

Good day ThrottleWorks


Please you have got the answer you want and also that you took the time to inform the members.


You do not need it now but one way to do it would have been text to columns,


Select all of Column AJ2, then text to Columns.

Then in the dialogue box select the fixed width button. Then click next.

In the next dialogue box check your line brakes are where you want them, adjust as required. Then click next.

In the final dialogue box you will see the date column is filled black, click the last radio button to select " Do not import column (skip)" , click in the choose destination box and click the cell where you want the time column to start and then click finish.

 

[ThrottleWorks]

Good Day bobhc Sir,


Thanks for the wonderful solution, this is really good.


I am using this in my macro, I recorded the steps you advised.

It is working fine, once again, thanks a lot.


Columns("AJ:AJ").Select

Selection.TextToColumns Destination:=Range("AP1"), DataType:=xlFixedWidth, _

FieldInfo:=Array(Array(0, 9), Array(9, 1)), TrailingMinusNumbers:=True

 

[SirJB7]

@b(ut)ob(ut)hc

Hi, my friend!

Good afternoon.

I'm marking this topic as solved.

Regards!

PS: Just in case...

 

[bobhc]

@SirJB7

Hello my old friend

Not a problem. My thanks, If you think it is solved then I must be doing something right