formula error for date

hi everyone

In current excel file B5 cell having only date like 1,2,3.... but i tried to change it into full date format like 12/12/2021 but am unable to get this format instead of that am getting correct date & month year is not matching every cell getting 1990 as year unable to change year. kindly suppport get the solution for this error. below am mentioned code which i used for that and also i attached the excel file.

=IF(AND((COLUMN()-1)>31*($A$3-1),MONTH(DATE($A$2+INT(($A$3+$A$1)/12),IF((INT((COLUMN()-1)/31.001)+$A$1)>12,(INT((COLUMN()-1)/31.001)+$A$1-12),INT((COLUMN()-1)/31.001)+$A$1),ROUNDUP(MOD(COLUMN()-1,31.001),0)))=ROUNDUP(MOD($A$1+$A$3-1,12.01),0)),ROUNDUP(MOD(COLUMN()-1,31.001),0),"")
date.PNG
kindly support to get the answer

This is much more complicated than it needs to be. Please see simplified formulas in attached file. Also the formula in row 6 is too complicated and simply needs to refer to row 5, with custom formatting Ddd to display the day.


I have also updated your conditional formatting rules.

FORMAT all date cells (B5:NI5) to dd/mm/yyyy [though why as you have the month/year in the title]

Change cell alignment to vertical 90 degrees

That seems very complicated. I changed B5 formula to:

=IF(MOD(COLUMN(B$5)-2,31)>DAY(EOMONTH(DATE($A$2,$A$1,1),INT((COLUMN(B$5)-2)/31)))-1,"",EOMONTH(DATE($A$2,$A$1,1),INT((COLUMN(B$5)-2)/31)-1)+MOD(COLUMN(B$5)-2,31)+1)

I then changed B6 formula to:

=IF(B$5="","",VLOOKUP(WEEKDAY(B$5,2),Sheet3!$D$1:$E$7,2,0))

I copied B5:B6 formulas all the way across to NI5:NI6. Does this work for you now?

WBD

Effective but I think it can be further simplified; see my attachment in post #2

Two things:

1. I think you missed columns AG:NI which are hidden/shown when the month is changed with the triangles. Your current formula can't be copied across from AF5 to AG5.
2. Using the "ddd" format code is fine provided this doesn't get opened on a non-English OS - that's why I left the hard-coded English days

WBD

Originally Posted by WideBoyDixon

1. I think you missed columns AG:NI which are hidden/shown when the month is changed with the triangles. Your current formula can't be copied across from AF5 to AG5.

You are absolutely correct. I did not notice hidden columns and did not delve into the VBA. I ahd assumed that the month was always shown in the same columns but now that you have pointed this out I see that an entire year is laid out, and the month selection determines which columns are visible.

To fix this, the formula becomes even simpler, even trivial. Also changes are needed in the VBA, which simplify it. See attached revision.

Missed the replies to this somehow. I made a change to my original upload. See attached.

WBD