Jun 01 2021 08:15 AM
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!
Jun 01 2021 08:26 AM
Let's say the date 1997-11-10 is in cell B2.
The formula you want is
=DATE(YEAR(TODAY()),MONTH(B2),DAY(B2))
Jun 01 2021 08:35 AM
Jun 01 2021 08:46 AM
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?
Jun 01 2021 08:50 AM
Jun 01 2021 09:08 AM
Perhaps in formula which @Hans Vogelaar suggested you shall use DATEVALUE(B2) instead of B2
=DATE(YEAR(TODAY()),MONTH(DATEVALUE(B2)),DAY(DATEVALUE(B2)))
Jun 01 2021 09:12 AM
Jun 01 2021 10:04 AM
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"))
Jun 01 2021 10:10 AM
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.
Jun 01 2021 11:33 AM