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]

Sounds like DATE will help

=DATE(year,month,day)

=DATE(B6,1,1) will be 01/01/21

 

[Jayzon]

Sounds like DATE will help

=DATE(year,month,day)

=DATE(B6,1,1) will be 01/01/21

I get an #NAME? error when using : =DATE(Frontpage!B6.1.1) i tried chaning commas to dots, cell is formatted as a date.

 

[mrshl9898]

=DATE(Frontpage!B6,1,1)?

 

[Fluff]

What language version of Excel are you using

Also I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Jayzon]

=DATE(Frontpage!B6,1,1)?

Awesome, this actually worked from frontpage sheet to the January-2021 sheet, how do i then auto populate B6 in the february-2021 sheet with an added month 01-02-2021

 

[mrshl9898]

Just the same again with ,2,1

 

[Jayzon]

Just the same again with ,2,1

This is what worked from Frontpage to January : =DATO(Frontpage!B6;1;1) but when trying to do the next step with : =DATO(January-2021!B6;2;1) i get an #NAME? error and ' ' is added to 2021 so it ends up =DATO(January-'2021'!B6;2;1) still with the error.

 

[mrshl9898]

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.

 

[Fluff]

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

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