Data Import & Export

Convert Excel Date to DD.MM.YYYY Text for SAP Upload

Emma Brown Oct 1, 2026 869 views

Question details

The user needs to convert standard Excel dates into exact DD.MM.YYYY text strings so they can be successfully uploaded to SAP S/4HANA without reverting to serial numbers.

How to Convert an Excel Date to DD.MM.YYYY Text for SAP Upload

Product: Spreadsheet
Device & OS: not provided
Scenario: Preparing and formatting a dataset in a spreadsheet for export and subsequent import into SAP S/4HANA.
Observed behavior: Excel visually displays the date as 20.03.2024, but stores it as a sequential serial number (e.g., 45371). When the data is exported, the upload system reads the serial number instead of the required text date format.

Before you start

Identify the column containing your original dates and insert a new blank column next to it where the converted text values will be generated.

Solution 1Recommended

Use the TEXT Function to Convert Dates to Text Strings

This method converts the underlying Excel serial number into a hardcoded text string, which is strictly required for accurate SAP data uploads.

Changing the visual format of a cell does not change the underlying data. To ensure SAP reads the date correctly, you must use a formula that generates actual text.

Enter the TEXT formula

Select the first cell in your new blank column. Type the formula =TEXT(A2,"dd.mm.yyyy") (assuming your original date is in cell A2) and press Enter.

Apply to all rows

Click the cell with the formula, then click and drag the small square (fill handle) at the bottom-right corner down to apply this formula to all required rows.

Convert formulas to plain values

Select the entire column of newly generated text dates, copy them (Ctrl+C), right-click the same selection, and choose 'Paste Special' > 'Values'. This removes the formula and leaves only the raw text.

Export and verify

Save your file in the required upload format (like CSV or TXT). Before uploading to SAP, open the exported file in a plain-text editor (such as Notepad) to verify that the dates appear as 20.03.2024.

Use the TEXT Function to Convert Dates to Text Strings

Data Verification: Pasting as values ensures that no formula references break during the export process, guaranteeing a clean upload into SAP.

Solution 2

Apply Custom Formatting for Visual Display

Use this method if you only need the dates to visually display as DD.MM.YYYY in your spreadsheet for reporting purposes, without changing the underlying serial number.

Efficient Data Processing

Prepare Data for SAP Uploads with WPS Spreadsheet

WPS Spreadsheet provides seamless support for advanced data formatting and formula execution, including the TEXT function, making it the perfect tool to prepare your datasets for enterprise system uploads.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing the dates you need to convert.
2. Apply the conversion formula: Use the =TEXT(cell, "dd.mm.yyyy") formula to instantly turn standard dates into formatted text strings.
3. Export to text format: Click on Menu, select 'Save As', and choose CSV (Comma delimited) or Text (Tab delimited) to prepare the file for SAP.

Fully compatible with Microsoft Excel formulas, functions, and custom formattingEasily save and export spreadsheets to CSV or plain text for seamless SAP uploadsLightweight application with fast processing speeds for large enterprise datasetsFree and highly intuitive interface for everyday data processing tasks

Frequently Asked Questions

Why does my Excel date show up as a 5-digit number when exported?

Spreadsheet software stores dates as sequential serial numbers (e.g., 45371) to allow for mathematical calculations like adding days. When you export data without converting it to text first, the system reads the underlying serial number instead of the formatted visual date.

Can I export directly to a text file after using the TEXT formula?

Yes. Once you have used the TEXT formula and pasted the results as values, you can save the spreadsheet as a CSV or Tab-delimited text file. It is highly recommended to open this exported file in a plain-text editor like Notepad to double-check the formatting before executing the SAP upload.

Does the TEXT function work the same way in WPS Office as it does in Microsoft Excel?

Yes, the =TEXT() function works identically in WPS Spreadsheet and Microsoft Excel. Both programs use the same syntax and custom format codes, ensuring full compatibility when processing and exporting your data.

Excel & Data Problems