Return current date for year

[catsrock1023]

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!

[#2]

@catsrock1023

Let's say the date 1997-11-10 is in cell B2.

The formula you want is

=DATE(YEAR(TODAY()),MONTH(B2),DAY(B2))

[#3]

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?

[#4]

@catsrock1023 

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?

[#5]

it's difficult to explain, but i'm not using excel for this, just need an excel formula (https://generalcaster.app/website/documentation/formula/ is the app i am using)

that doesn't really answer your question, i'm sorry!

[#6]

@catsrock1023 

Perhaps in formula which @Hans Vogelaar suggested you shall use DATEVALUE(B2) instead of B2

=DATE(YEAR(TODAY()),MONTH(DATEVALUE(B2)),DAY(DATEVALUE(B2)))

[#7]

That kind of worked, but I think there was an error because it needs to be in YYYY-MM-DD format. Any suggestions?

[#8]

@catsrock1023 

Sorry, I don't know.

[#9]

no worries, thank you for the help!

[#10]

@catsrock1023 

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"))

[#11]

@Peter Bartholomew 

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.

[#12]

Thanks. I had picked up that another system came into the picture but I (wrongly) assumed it was an import/export issue.