Using Text Dates in Excel

If you need to enter a date in a formula you typically use the DATE function to create the date. You can enter the date as Text and Excel will convert it for you.

In general, you should avoid typing values (including dates) into a formula if that value could change. Enter the value into a labelled cell and refer to that cell in the formula.

Maybe you need to create a self-contained formula that refers to a specific date.

In the image below you can see a formula that is deducting the 1 January 2025 from today’s date. I am writing this on 6 Jan 2025.

The DATE function uses the following syntax.

DATE(Year,Month,Day)

Year, Month and Day are entered as whole numbers.

You can avoid the DATE function and just type the date enclosed in quotation marks – see image below.

The downside of this technique is that because Excel has converted the text date it also assumes the result is a date. Excel automatically formats the cell as a Date.

When you remove the Date format the correct value is displayed.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 thoughts on “Using Text Dates in Excel

  1. If you write your formula this way, it looks like it outputs the value in General format directly…

    =N(TODAY()-“1-1-2025”)