Auto populate date

[Jayzon]

Hi
Is there a way to auto populate cells with a date based on the year entered in another.
In the frotpage sheet i enter 2021 in B6 and then i would like B6 in the January-2021 sheet to auto populate with the date 01-01-2021.
And based on B6 in the January-2021 sheet i would like B6 in February-2021 to auto populate with the date 01-02-2021.

 

[mrshl9898]

Cheers, was not aware.

 

[Jayzon]

Just use

=DATE(Frontpage!B6,2,1)

If you use a date as the year you'll need

=DATE(YEAR(January-2021!B6),2,1)

Also, be careful, you need commas. Not semicolon or full stops in functions.

I am on a danish version of excel the first formula worked when i changed the commas with semicolons, i am not having the same luck with this. =DATE(YEAR(January-2021!B6),2,1) i get the same error.

No matter what i try it adds the ' ' to 2021 and ,2,1 is marked up in gray.

 

[Fluff]

Try it like

=DATE(YEAR('January-2021'!B6),2,1)

or in Danish

=DATO(ÅR('January-2021'!B6);2;1)

 

[Jayzon]

That depends on the local settings & language version. A lot of countries use semi-colons as separators in formulae

No matter what i try with =DATE(YEAR(January-2021!B6),2,1) i get the #NAME? error, it adds ' ' to 2021 and marks ,2,1 in a gray box.

The danish equivalent of this should be =DATO(ÅR(January-2021!B6);1;2) based on the previous syntax

 

[Jayzon]

Try it like

=DATE(YEAR('January-2021'!B6),2,1)

or in Danish

=DATO(ÅR('January-2021'!B6);2;1)

That did the trick.

Thanks all for the help.