Forum Discussion
Return current date for year
I need some help building a formula. I need to return the current year of a date in a cell in the YYYY-MM-DD format. For example, in one cell I have 1997-11-10 and I need it to return 2021-11-10 in another cell.
Thank you in advance!
11 Replies
I don't fully understand where the problem arises. Excel formulas accept date serial numbers but with the DATEVALUE function it will also accept formats appropriate to the machine localisation which should include ISO standard dates. So
= DATE( 2021, MONTH(DATEVALUE(B2)), DAY(DATEVALUE(B2)) )should give a serial date that can be formatted. As an alternative if the cycle is text to text one could use
= LET( old, DATEVALUE(oldDate), mnth, MONTH(old), d, DAY(old), current, DATE( 2021, mnth, d), TEXT(current,"YYY-MM-DD"))That's not Excel, that is this app https://generalcaster.app/website/documentation/formula/ which uses formulas as in Excel, but not all and by bit different way.
- Thanks. I had picked up that another system came into the picture but I (wrongly) assumed it was an import/export issue.
Let's say the date 1997-11-10 is in cell B2.
The formula you want is
=DATE(YEAR(TODAY()),MONTH(B2),DAY(B2))
- what if it's a string of text? i'm using this formula in a different program that supports the use of excel formulas. it uses the ISO 8601 date but projects it as "Nov 11, 1997"
I tried the above formula with no success, I think something similar to this:
CONCATENATE(YEAR(TODAY()), "-", TEXT(DATEVALUE(B2), "YYYY-MM-DD")
should work but is not working lol. any suggestions based on this info?Do you mean that the cell (B2 in this example) contains a text value? What do you see in the formula bar when you select that cell?
