# Return current date for year

Occasional Contributor

# 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

# Re: Return current date for year

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

# Re: Return current date for year

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?

# Re: Return current date for year

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?

# Re: Return current date for year

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!

# Re: Return current date for year

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

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

# Re: Return current date for year

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

# Re: Return current date for year

Sorry, I don't know.

# Re: Return current date for year

no worries, thank you for the help!

# Re: Return current date for year

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

# Re: Return current date for year

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.

# Re: Return current date for year

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