Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SAP - Excel date time stamp conversioN

Posted on 2014-04-22
4
3,333 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

792 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