Doing Date Math on Text (non real) Dates
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
…which returns the serial number of (in this example) the date in cell C3 + 1, which is July 18, 2013.
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.
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
• Click the OK button
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1
• 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
Leave a Comment