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

Download Workbook

4 Ways to Convert SAP Timestamp to Date in Excel

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 Excel 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 (2 Suitable Cases)

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 a 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.

Read More: How to Convert Active Directory Timestamp to Date in Excel (4 Methods)

Similar Readings

• Convert Text to Date and Time in Excel (5 Methods)
• How to Convert General Format to Date in Excel (7 Methods)
• Convert Text to Date in Excel (10 ways)
• How to Convert Number to Date in Excel
• How to Convert Serial Number to Date in Excel (7 Simple Ways)

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

In this section, we will use the DATE function, LEFT function, MID function 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: Convert Text Date and Time to Date Format in Excel (7 Easy Ways)

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 also 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 are having 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.

Read More: How to Convert Week Number to Date in Excel (2 Suitable Methods)

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 UNIX Timestamp to date for the following Order Times. UNIX Timestamp basically tracks time in a 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 got the corresponding dates for the UNIX Timestamp values.

Read More: How to Convert Unix Timestamp to Date in Excel (3 Methods)

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.

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 Insert Static Date in Excel (4 Simple Methods)
• Text Won’t Convert to Date in Excel (4 Problems & Solutions)
• How to Disable Auto Convert to Date in Excel (2 Methods)
• Excel VBA to Convert Date and Time to Date Only
• How to Convert Date to Julian Date in Excel (3 Easy Ways)
• Convert 13 Digit Timestamp to Date Time in Excel (3 Ways)
• How to Convert Date to Quarter and Year in Excel