Not able to convert into date format

[Prabhu9teen]

Hi All,

The below is the data downloaded from ERP, and i like to extract the date from the below data.

also i have uploaded a copy of the file, which is customized to show the proper date.

3/23/1900 10:12:23 PM.

Let me know if you have any questions.

 

[Marc L]

Hi,

just change the cell format !
​

 

[Prabhu9teen]

Thanks for the response Marc!

i'm looking out for a formula (no VBA if possible)to extract date, month and year (MM/DD/YYYY) from the below cell , such as "01/02/2015" ?

i tried changing the format as well, it is showing 03/23/00 instead of "01/02/2015".

 

[NARAYANK991]

Hi Prabhu ,

See this file.

Narayan

 

[Prabhu9teen]

Wow... Great thanks NarayanK!!

=DATE(INT(A2*24),MINUTE(A2),SECOND(A2))

the above formulas is also working for me!!

=TEXT(A2,"[h]:mm:ss") - This formula is new to me, Great learning for the day! thank you once again!!

 

[Khalid NGO]

Hi,

Just adding another one:
=DATEVALUE(SUBSTITUTE(TEXT(A2,"[h]:mm:ss"),":","/"))

Change format to date.
Regards,

 

[Prabhu9teen]

Thanks Khalid