• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4207
  • Last Modified:

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!!
0
BajanPaul
Asked:
BajanPaul
  • 2
2 Solutions
 
Naresh PatelTraderCommented:
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
 
Saqib Husain, SyedEngineerCommented:
Try

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

And format the cell as a date
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now