Solved

SAP - Excel date time stamp conversioN

Posted on 2014-04-22
4
3,254 Views
Last Modified: 2014-04-22
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
Comment
Question by:BajanPaul
  • 2
4 Comments
 
LVL 8

Assisted Solution

by:itjockey
itjockey earned 250 total points
ID: 40015495
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
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 250 total points
ID: 40015497
Try

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

And format the cell as a date
0
 
LVL 8

Expert Comment

by:itjockey
ID: 40015516
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
 

Author Closing Comment

by:BajanPaul
ID: 40015518
Both solutions worked perfectly.. Thanks Greatly!!
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This collection of functions covers all the normal rounding methods of just about any numeric value.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question