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.

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.

convert sap timestamp to date in Excel

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.

convert sap timestamp to date in Excel

Steps:
➤ Type the following formula in cell E4

=TEXT(C4,"0000-00-00 00\:00\:00")+0

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.

TEXT function

➤ Press ENTER and drag down the Fill Handle tool.

convert sap timestamp to date in Excel

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

TEXT function

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

TEXT function

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

convert sap timestamp to date in Excel

Read More: How to Convert Timestamp to Date in Excel (7 Easy Ways)


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.

convert sap timestamp to date in Excel

Steps:
➤ Type the following formula in cell D4

=TEXT(C4,"0000-00-00 00\:00\:00")

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

TEXT & DATEVALUE function

➤ Press ENTER and drag down the Fill Handle tool.

convert sap timestamp to date in Excel

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.

TEXT & DATEVALUE function

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

=DATEVALUE(D4)

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

TEXT & DATEVALUE function

➤ Press ENTER and drag down the Fill Handle tool.

TEXT & DATEVALUE function

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

convert sap timestamp to date in Excel

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


Similar Readings


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.

convert sap timestamp to date in Excel

Steps:
➤ Type the following formula in cell E4

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

Here, C4 is the SAP Timestamp value.

  • LEFT(C4,4) → 2022
  • MID(C4,5,2) → 02
  • MID(C4,7,2) → 28
  • DATE(LEFT(C4,4),MID(C4,5,2),MID(C4,7,2)) becomes
    DATE(2022,02,28)
  • Output → 2/28/2022

DATE function

➤ Press ENTER and drag down the Fill Handle tool.

DATE function

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

convert sap timestamp to date in Excel

Related Content: How to Convert Unix Timestamp to Date in Excel (3 Methods)


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.

convert sap timestamp to date in Excel

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

Text to columns option

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

convert sap timestamp to date in Excel

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.

Text to columns option

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.

Text to columns option

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

Text to columns option

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.

Text to columns option

➤ Type the following formula in cell H4

=DATE(D4,E4,F4)

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

Text to columns option

➤ Press ENTER and drag down the Fill Handle tool.

Text to columns option

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

convert sap timestamp to date in Excel

Read More: Text Won’t Convert to Date in Excel (4 Problems & Solutions)


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.

convert sap timestamp to date in Excel

Steps:
➤ Type the following formula in cell E4

=(C4/86400)+DATE(1970,1,1)

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.

UNIX Timestamp

➤ Press ENTER and drag down the Fill Handle tool.

UNIX Timestamp

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

convert sap timestamp to date in Excel


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.

Practice

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

Practice


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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo