How to Convert Text to Date in Excel, Whatever the Export Format
Turn export dates stored as text into real Excel dates: ISO timestamps, swapped day and month, Unix time, and how to remove the time from a date.
- Excel
- Data Quality
- Formulas

On this page
- How do you know if a date is stored as text?
- How do you convert text to a date in Excel?
- How do you convert a timestamp like 2026-03-04T14:22:01Z?
- How do you remove the time from a date in Excel?
- Why are the day and month swapped?
- How do you convert a Unix timestamp to a date in Excel?
- How do you fix the date column every month with Power Query?
- How do you convert text to a date in Google Sheets?
- Where does Glow Reports fit?
- Common questions
You open an export from Stripe, Shopify or your CRM, and the date column won't behave. It doesn't sort by date, the pivot table refuses to group it by month, and changing the format to Short Date does nothing. The cells hold text that looks like a date, such as or . To fix it, turn the text into a real Excel date: a number Excel can sort, filter and group. Here is how to spot a text date, then the formula for each format exports use, and the Power Query setup that fixes the column every month.
How do you know if a date is stored as text?
Type next to the date. A real date returns TRUE, because Excel stores every date as a number. A text date returns FALSE. Text dates also sit on the left of the cell by default, while real dates sit on the right.
Excel counts days from 1 January 1900, which is serial number 1 Microsoft Support. 4 March 2026 is 46085. The time is the decimal part of the day, so noon is 0.5 Microsoft Support. 4 March 2026 at 14:22:01 is 46085.5986.
That explains why the format menu fails. HomeNumber FormatShort Date changes how a number is displayed. A text cell has no number behind it, so there is nothing to display differently. The ISNUMBER and ISTEXT functions are the quickest test on a whole column Microsoft Support. Fill down and filter on FALSE to see every row that needs fixing.
Two-digit years get a green triangle in the corner of the cell. Select the cells, open the error button and pick Convert XX to 20XX Microsoft Support.
How do you convert text to a date in Excel?
Use when the text matches your computer's date settings, then format the result as a date. DATEVALUE returns the serial number of a date stored as text, or #VALUE! when it can't read it. It also drops any time in the text Microsoft Support.
To keep the time as well, use . VALUE reads any number, date or time format Excel recognizes, so becomes one number with the time in its decimal part Microsoft Support.
Then make the fix permanent:
- Put the formula in a helper column next to the text dates and fill it down.
- Format the helper column with HomeNumber FormatShort Date, or press Ctrl+1 for a custom format such as Microsoft Support.
- Copy the helper column and paste it over the original with Paste SpecialValues.
- Delete the helper column and check again.
If DATEVALUE and VALUE both return #VALUE!, the text isn't in a format your settings recognize. The next sections cover the usual culprits.
How do you convert a timestamp like 2026-03-04T14:22:01Z?
Cut the text into its parts and rebuild the date with DATE and TIME. Those two functions take numbers, not text in a regional format, so the result is the same on any computer. The T separates the date from the time, and the Z means the time is in UTC.
=DATE(LEFT(A2,4),MID(A2,6,2),MID(A2,9,2))+TIME(MID(A2,12,2),MID(A2,15,2),MID(A2,18,2))LEFT takes the year, the first MID the month, the second the day. The TIME part takes hours, minutes and seconds from positions 12, 15 and 18. DATE takes a year, a month from 1 to 12 and a day Microsoft Support. The same formula works for without the T, because the positions don't change.
UTC can move a late-evening order to the next day in your time zone, and into the next month on the last day of a month. To show Paris time in winter, one hour ahead of UTC, add to the formula. Add in summer. For a month-end report, decide which time zone counts before you total anything.
How do you remove the time from a date in Excel?
Once the cell is a real date, keeps the day and drops the time. INT rounds a number down to the nearest whole number Microsoft Support, and the whole number is the day.
Formatting the cell as a date hides the time but keeps it. That matters in formulas. A SUMIFS that looks for 4 March 2026 compares against 46085 and skips 46085.5986, so every order with a time drops out of the daily total. The same happens with an end date in a range: "on or before 31 March" stops at midnight and misses the last day. Use INT on the column, or compare with "before 1 April" instead.
If the cell is still text, take the date part only: for timestamps that start with the year.
Why are the day and month swapped?
Because the export uses one order and your computer another. A US file writes 03/04/2026 for 4 March. Opened with UK or French settings, Excel reads it as 3 April, with no error. When the day is above 12, as in 03/22/2026, there is no month 22, so the cell stays text.
The giveaway is a column where some dates sit on the right and others on the left. The right-aligned ones are wrong dates, and the left-aligned ones are text. Don't fix the text cells alone. The converted cells are already wrong.
Go back to the original file instead and import it with the right order. Power Query does this through the locale. Right-click the column, select Change TypeUsing Locale, pick Date or Date/Time and the locale the export was made in, such as English (United States) Microsoft Learn. For a quick fix on text that is all in the same month/day/year shape with two digits each, works too. The cleaning guide shows the Text to Columns route for the same problem.
How do you convert a Unix timestamp to a date in Excel?
Divide by the number of seconds in a day and add the start of Unix time: . A Unix timestamp counts seconds since 1 January 1970 in UTC. 1772634121 becomes 4 March 2026 at 14:22:01 UTC.
Some APIs and exports count milliseconds instead, a 13-digit number such as 1772634121000. Divide by 86400000 in that case. If the cell still shows ##### after you widen the column, you used the seconds formula on milliseconds and the date landed past the year 9999. Format the cell as a date and time, and add the time-zone offset from the previous section if your report runs on local time.
How do you fix the date column every month with Power Query?
Import the file with DataFrom Text/CSV and select Transform Data instead of Load. Set the date column's type with Change TypeUsing Locale, then close and load. Next month, replace the file and select DataRefresh All. Power Query repeats the conversion.
Power Query uses your system's regional format to read text by default, which is why a US file fails on a UK or French computer Microsoft Learn. Setting the locale on the column fixes that for good. When each month arrives as a new file, load the whole folder at once, as in how to merge Excel and CSV files with Power Query. For timestamps with a T and a Z, the Date/Time/Timezone type holds the UTC time with its offset, and becomes a plain date and time when loaded.
How do you convert text to a date in Google Sheets?
The same formulas work: DATEVALUE, VALUE, DATE with LEFT and MID. Google Sheets reads date text according to the spreadsheet's locale, so a US date fails in a sheet set to France. Check it in FileSettings, under Locale Google Docs Editors Help.
DATEVALUE in Sheets returns a number. Change the cell format to Date to see it as one. It understands the formats Sheets converts on its own when you type them in a cell, and those depend on region and language Google Docs Editors Help. Type one example date in an empty cell to test whether Sheets reads that format.
Where does Glow Reports fit?
The formulas above fix one column in one file. The work grows when every export writes dates its own way, Stripe in UTC, the CRM in US order, the shop in local time, and the monthly report needs all three on the same calendar.
With Glow Reports, you upload the CSV or Excel files and ask for the report in plain language, such as revenue by month. A CSV upload detects its separator, comma, semicolon or tab. The AI writes the SQL, builds the charts and drafts the text. The SQL sits under each figure, so you can see how the date column was read and grouped, and edit the query if a month boundary or time zone needs to change. Reports export to PDF and stay in the workspace. The free plan gives 300 credits once, and a report costs 100 credits.
Common questions
Why does changing the date format in Excel do nothing?
Because the cell holds text, not a date. A format changes how a number looks, and text has no number behind it. Convert the text with DATEVALUE, VALUE or DATE first, then apply the format.
Why does DATEVALUE return #VALUE!?
The text isn't in a date format your computer's settings recognize, or the cell already holds a real date. Check with . For a timestamp with a T or a date in another country's order, rebuild it with DATE, LEFT and MID instead.
How do I convert a date to text in a fixed format?
Use TEXT with a format code, such as . The result is text again, so keep it for labels and file names, not for sorting or totals Microsoft Support.
Why does Excel change my dates when I open a CSV?
Excel converts date-like values as it opens the file, using your regional settings. A US file opened with UK settings swaps day and month. Import it with DataFrom Text/CSV and set the locale, as shown in CSV to Excel when the file opens in one column.
Can I group text dates by month in a pivot table?
No. A pivot table groups only when every value in the field is a real date, so one text date blocks the whole column. Convert the column first, then follow how to group a pivot table by month.
I am a product engineer and the founder of Glow Reports. I spent more than 6 years building products in tech, data and cybersecurity, for large groups, startups and SMEs. In most organizations I saw data treated as a technical topic, when it drives business decisions. I built the reporting tool I was looking for, so the people who run small and mid-sized organizations can read their own numbers without a data team.
More from El Mehdi BenaneUpload your CSV or Excel exports to Glow Reports and ask for revenue by month, with the SQL behind each figure.


