How to Convert SAP Timestamp to Date in Excel (4 Ways)

If you are looking for some of the easiest ways to convert SAP Timestamp to date in Excel easily, then you will find this article useful. SAP Timestamp has a format YYYYMMDDhhmmss in which date and time combine together. So, after going through this article, you will be able to easily extract the dates in date format from this SAP Timestamp.

How to Convert SAP Timestamp to Date in Excel: 4 Ways

Here, we have the following table containing the sales records of a company. It has some SAP Timestamp formats in the Order Time column and we will convert them into dates using the following methods.

We have used Microsoft 365 version here; you can use any other versions according to your convenience.

Method-1: Using TEXT Function to Convert SAP Timestamp to Date

Here, we will use the TEXT function to convert the SAP Timestamp formats of the Order Time column into dates.

Steps:
➤ Type the following formula in cell E4.

TEXT will convert the value in cell C4 into a text in the given format and then we have added 0 with it to convert the text again into value.

➤ Press ENTER and drag down the Fill Handle tool.

Then you will get the values of the corresponding dates and so now you have to change the General format into Date.

➤ Select the range of the Date column and go to the Home Tab >> Number Group >> select the Short Date format from the Number Format dropdown box.

After that, you will get the dates from SAP Timestamp in the Date column.

Read More: How to Convert Timestamp to Date in Excel

Method-2: Using DATEVALUE Function with TEXT to Convert SAP Timestamp to Date

In this section, we will use the DATEVALUE function along with the TEXT function to convert SAP Timestamp to dates.

Steps:
➤ Type the following formula in cell D4.

TEXT will convert the value in cell C4 into text in the given format.

➤ Press ENTER and drag down the Fill Handle tool.

In this way, we have converted the Timestamps of the Order Time column into text strings in the format YYYY-MM-DD hh: mm: ss.

Now, we will convert the text strings of the Text column into dates by using the DATEVALUE function.
➤ Type the following formula in cell E4.

Here, DATEVALUE will convert the text string of D4 into a date.

➤ Press ENTER and drag down the Fill Handle tool.

Finally, we will convert the SAP Timestamp values of the Order Time column into corresponding dates.

Method-3: Using DATE, LEFT, MID Functions to Convert SAP Timestamp to Date in Excel

In this section, we will use the DATE, LEFT, and MID functions to change Timestamp into a date.

Steps:
➤ Type the following formula in cell E4.

Here, C4 is the SAP Timestamp value.

➤ Press ENTER and drag down the Fill Handle tool.

Ultimately, we are getting the corresponding dates of the Timestamp values of the Order Time column.

Read More: How to Convert 13 Digit Timestamp to Date Time in Excel

Method-4: Text to Columns Feature to Convert SAP Timestamp to Date

You can use the Text to Columns option also to get dates from the corresponding Timestamp values easily. For this purpose, we have added some blank columns which will have the separated Year, Month, Day, Time, etc.

Steps:
➤ Select the range of the Order Time column and then go to Data Tab >> Data Tools Group >> Text to Columns Option.

After that, the Convert Text to Column Wizard will pop up.
➤ Select the Fixed width option and click Next.

Then, you will be taken to the next stage.
➤ You have to click at your desired position to create a break line according to which the values will be split into different columns and so here we have clicked after the first 4 values to separate the year.

In the same process, create the break lines for separating month and day and then select the Next option to go to the final stage.

➤ Now, select General as the Column data format option, type the name of the cell $D$4 for the Destination option, and finally click Finish.

After completing the 3 stages, we have separate years, months, days, and times and now using the DATE function we will create the dates.

➤ Type the following formula in cell H4.

Here, D4 is the year, E4 is the month and F4 is the day.

➤ Press ENTER and drag down the Fill Handle tool.

Afterward, we will get the dates in the Date column.

Converting UNIX Timestamp to Date in Excel

Another common timestamp format is the UNIX format. To ease your effort, we’re adding a basic and simple method to convert UNIX Timestamp to a conventional date format.

Here, we will consider an additional case for converting the UNIX Timestamp to date for the following Order Times. UNIX Timestamp basically tracks time in the form of seconds and this counting starts from the “UNIX Epoch” on 1/1/1970 to the current time or your given time.

Steps:
➤ Type the following formula in cell E4.

Here, C4 is the UNIX Timestamp value and after dividing it by 86400 we are converting the seconds to days (as 1 day = 86400 seconds) and then adding it with the date 1/1/1970 will convert our UNIX Timestamp into a date.

➤ Press ENTER and drag down the Fill Handle tool.

As a result, we can see that we have the corresponding dates for the UNIX Timestamp values.

Practice Section

For doing practice by yourself we have provided the following Practice sections in the sheets named Practice1 and Practice2. Please do it by yourself.

Practice1 for converting SAP Timestamp, and Practice2 for UNIX Timestamp.

Download Workbook

Conclusion

In this article, we tried to cover the ways to convert SAP Timestamp to date in Excel easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

Related Articles

• How to Convert Week Number to Date in Excel
• How to Convert Active Directory Timestamp to Date in Excel

<< Go Back to Convert to Date | Date-Time in Excel | Learn Excel