Doing Date Math on Text (non real) Dates - KING OF EXCEL

KING OF EXCEL

KINGEXCEL.INFO ( KING OF EXCEL ) Welcome KINGEXCEL.INFO - Nothing Is Unable ... About Excel Tricks, Learning VBA Programming, Dedicated Software, Accounting, Living Skills ...

Wednesday, December 18, 2019

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 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.
📤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

No comments:

Post a Comment