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
![DateMath_Text_Error](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_sbpsqYxOXzVWEU79jpRp0_83RArdDhR3ZCbAvbXmlwEm732g1eo2i_tu0OnrzyFVYC5DEheKuBcP7Moip9FPKgGFaCguuAsUFdjlKkbaf5w7PKdrubzLNdgrEUytlnHBnT5X9WfO98JYPepQ=s0-d)
#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.
![ExtractionFormulaForTextDateMath](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_tDpiFXoiIpD28FDakF1wLg7oSI_LRCKQZPGhwF-ttceLZ4MUsWZ7BRpIwSBQoGcIJqfCFUiGbaUWEmQ7Bi_-sSetg68_QU9kjB9AHhWCuX-KcyhanHAlQypGzFU43Jd_hYyQnReTb7crgi4RuPOc-bp-D5L2xta31nGg=s0-d)
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.
![RightClickCell_FormatCells](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_uly_qLJd6i3xaw-5z7BReAy4YJLPDEe3ZdI6Gyl7AhJcjYfnVewIGjfYXXbcxks41eWDiyKZ2jZb-ZGSh4RC2vnGXzdOnmbfSv3RCHL6iX-vOtoogRrbwSZ27hMRML9D-hnI5YTqFK9K_O0bVRIFF3-XxQWg=s0-d)
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
![FormatCells_Custom_Date](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_vnDTEA13Nl9H6AWqX8-eVThf8vrunlEWSRdROh4nHcROzZzfXqZr4utM0WP44LFu08ZU3TU1vGcRZgPLrooGfXkSACvJlzF2t7vnDO8VnNq1hCU3ISrKp7UeaWQLvGpFyj_QBFRPwcOpPZrQyfVvNn=s0-d)
📤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
No comments:
Post a Comment