How to convert SAP generated date to MS excel true date

[Megelle]

One of my SAP report download that I copied into MS excel sheet, has a date column which shows sample dates like this '79779794'. How to convert it into true date in EXCEL? From the first look, this 8 digit SAP date looks like ddmmyyyy format in EXCEL. But, it just gives me a long line of ########################## , like this as an output for all types of date format options in EXCEL.

 

[steve the fish]

Thats just excel giving you an error. Dates in excel are whole numbers whereby 1/1/1900 is 1 and today as an example is 44599 so 44598 days after the 1st Jan 1900. Formatting your cell to dd/mm/yyyy will just tell excel to use the 79 millionth day from 1/1/1900. The maximum year you can use in excel is 9999 hence your error. What you need to do is understand what that SAP date represents because it isnt ddmmyyyy.

 

[Megelle]

SAP's date column header's name for 79779794 is just a simple "Valid from" Date. As in, it is pulling SAP data for a particular date range.(from a "valid date" till date).

I followed these actions.
1. Downloaded the SAP data (with valid from dates and other currency info) in tabular form to text file.
2. Copied downloaded data from text file to EXCEL and formatted table with delimiters. All other info except the data column is ready to be used for further processing.
3. I still see the same 79779794 data (that was in SAP) in EXCEL with no errors (##############).
4. But when I try changing to any data format type to get a true date, it errors out with a #################### in EXCEL cells.

I tried the above steps by downloading in different available formats in SAP.(RTF, clipboard, text file, HTML).

Is there a way out or a formula to get the true date in EXCEL, just with the 79779794?

 

[steve the fish]

Not until you find out how 79779794 is generated. You could then apply a formula to convert it. Currently its just a large number to excel and no formatting is going to help. I would contact SAP and ask them.