Edit a cell's value from "01/01/2022" to "01/01/2023" without changing its ISTEXT status

Hi, this is seemingly a very simple task but I can't seem to get around it.

Keep in mind that the initial format of the date cells must remain the same.

That is, for example, =ISTEXT(B1)=TRUE and ISNUMBER(B1)=FALSE. The format in the home tab should show "Date", as it is currently.

For me, any attempt to transform 01/01/2022 (cell B1), into 01/01/2023, changes the formatting (the status of ISTEXT and ISNUMBER), which is what I'm trying to avoid.

There might be some funky cell formatting going on that causes the issue, but I can't seem to put my finger on it. Thanks again for any help/advice.

Without using macros, you can create a row for formulas

=SUBSTITUTE(B1,"2022","2023")

copied across to match your 'dates' - copy the formulas and pastespecial values over your dates.

Otherwise, a macro is easy

Please Login or Register  to view this content.

Re: "I don't think it's related to the space at the end of the cell, because if I try to TRIM it it still won't work."

If you add a non-breaking space, TRIM will not remove it - it only removes Character 32 spaces. You could, of course, use DATEVALUE(LEFT(cell, LEN(cell)-1))

Can I ask a stupid question? Why do you want to retain the date as TEXT, rather than as a real date?

You're the ONLY person (that I recall) who wanted this outcome!!

Originally Posted by bibu

Thanks for this clarification.
By the way, I've tested the VBA solution and it doesn't seem to work. Did it work for you? I think you can't set the Text property of a cell. I think it's read-only.

You're correct - I should have used c.Value instead of the first c.Text.

Sorry about that.

Originally Posted by Glenn Kennedy

Can I ask a stupid question? Why do you want to retain the date as TEXT, rather than as a real date?

You're the ONLY person (that I recall) who wanted this outcome!!

Yes, you most certainly can. I want to retain this weird format because there are macros that copy info from this file, into other files. The copying is done based on the selected period (the row with the dates that is the subject of this topic). So, since the macros have been "custom made" to work with the current date format, changing the format would mean that the macros will fail.
So I'd rather keep it as it is.
Also, I've done some digging because I was curious to know how did that row get formatted like this. It seems they were initially dates, but then the sheet contents were formatted as table (with the date row as header), and this changed its formatting.

That makes sense, sort of. It underlies my dislike of Excel Tables... which "know better than you" and insist that headers are Text.