SAP - Excel date time stamp conversioN

Deal Excel Gurus i need your help,
I am exporting data out of SAP for their time stamp and it is in this format 20,140,227,000,702

I read a blog or two and saw the following:

2014 year
02 Month
27 Date

00 hrs
07Min
02 Seconds

I am not interested in the time portion, just want to extract the date in either one column with a single function Month/Day/Yr or Year/Month/Date either way is fine.


Thanks for the help!!
BajanPaulAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Try

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))

And format the cell as a date
0
 
Naresh PatelConnect With a Mentor TraderCommented:
Assuming
20,140,227,000,702
in Cell A1 then formula in B1=LEFT(SUBSTITUTE(A1,",",""),4)&"-"&MID(SUBSTITUTE(A1,",",""),5,2)&"-"&MID(SUBSTITUTE(A1,",",""),7,2).


Thanks
0
 
Naresh PatelTraderCommented:
MMM-DD-YYYY format =TEXT((LEFT(SUBSTITUTE(A1,",",""),4)&"-"&MID(SUBSTITUTE(A1,",",""),5,2)&"-"&MID(SUBSTITUTE(A1,",",""),7,2))*1,"MMM-DD-YYYY")

Thanks
0
 
BajanPaulAuthor Commented:
Both solutions worked perfectly.. Thanks Greatly!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.