Tom’s Tutorials For Excel: Doing Date Math on Text (non real) Dates
In yesterday’s tutorial, I showed how to place a date and time on separate lines in the same cell.
That example involved a formula with the TEXT
function, meaning the date and time value in cell A3 was not a real date or real time, but a constant text value that was not unlike a text sentence such as you are reading now.
Common wisdom has it that you cannot perform mathematical functions on text dates. For example, if you try to add a 1 to the text value, you will return a #VALUE!
error.
However, you start to have some luck with a formula to return all to the left of CHAR(10)
…
=(LEFT(A3,FIND(CHAR(10),A3)-1)+0)+1
…which returns the serial number of (in this example) the date in cell C3 + 1, which is July 18, 2013.
All that remains is to format the cell for a readable date.
Right-click the cell and select Format Cells from the popup menu.
In the Format Cells dialog box:
• Click onto the Number tab.
• In the Category list box, select Custom.
• In the Type field, enter your desired date format, such as MMMM D, YYYY
• Click the OK button
Your final result would look like this — date math successfully perfomed on a text non date value.
Hello Tom,
I entered the formula just as written above into cell C3, but keep coming up with the #VALUE! error. I gave up, thinking I may have typed something incorrectly and just copied and pasted it instead. It still gives me the error message though. Is there something I’m doing wrong? Can you help? I’m using Excel 2010.
Thanks in advance.
I know the formula works so my first question is, what exactly is in cell A3?