Staff Leave tracker template - Date formula breaks when adding additional columns (+12)

[LeaAnne]

Hi AliGW
I am trying to create a new POST and keep getting an error message saying that I cant post images, links etc until I have posted a few times.

I removed the attachment that I had uploaded, and this did not fix the problem.

--------------------------------------------------------------------------------------------------------------------------

NEW POST BELOW
thank you ALIGW for the date formula for adding the additional columns. see below

Change the date formula to this:

=IF(AND((COLUMN()-3)>31*($A$3-1),MONTH(DATE($A$2+INT(($A$3+$A$1)/12),IF((INT((COLUMN()-3)/31.001)+$A$1)>12,(INT((COLUMN()-3)/31.001)+$A$1-12),INT((COLUMN()-3)/31.001)+$A$1),ROUNDUP(MOD(COLUMN()-3,31.001),0)))=ROUNDUP(MOD($A$1+$A$3-1,12.01),0)),ROUNDUP(MOD(COLUMN()-3,31.001),0),"")

and drag copy across.

Each reference to COLUMN() needs -3 after it instead of -1.

----------------------------------------------------------------------------------------------------------------------
I have added an additional 12 columns, and changed the date Formula to (-13) and I have also edited the macro to LeaveTracker.Columns("N:NI").Hidden = True
Previously "B:NI"

This has worked fantastically for every month except the first month - January. Whereby 12 of the Days are missing (only Days 1-19 visible)

Clearly this is demonstrating my limitations in excel and I am hoping you are able to easily provide the solution for this.

many thanks in advance

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[AliGW]

I found your workbook attached to the hijacked thread.

You have columns AG to PJ hidden - unhide them and drag the formula in AF5 across. Then rehide the columns you don't need.

I cannot help with the macro - if you want help with that, start a new thread dedicated to the macro issue in the VBA section of the forum.