HELP! Formulas assistance

[Roo007]

i have an excel formula below that i need to add a column to after each day of the week,. How do i write/adjust the formula to give me what i need?
=IF(MONTH($K$6)<>MONTH($K$6-(WEEKDAY($K$6,1)-($AU$3-1))-IF((WEEKDAY($K$6,1)-($AU$3-1))<=0,7,0)+(ROW(K8)-ROW($K$8))*7+(COLUMN(K8)-COLUMN($K$8)+1)),"",$K$6-(WEEKDAY($K$6,1)-($AU$3-1))-IF((WEEKDAY($K$6,1)-($AU$3-1))<=0,7,0)+(ROW(K8)-ROW($K$8))*7+(COLUMN(K8)-COLUMN($K$8)+1))
You help is greatly appreciated!!!!

 

[ukmikeb]

Hi

Welcome to the MrExcel Forum.

What is in cell AU3?

What cell is the formula in?

What is the purpose of the formula?

 

[Roo007]

The original file comes from.... Perpetual Calendar Template - Free Perpetual Calendar (can download)
The AU# field is the same as the "start day"

 

[ukmikeb]

If K6 is 01/11/2015 and AU3 is 1 then the result is 01/11/2015.

What cell is the formula in?

What is the purpose of the formula?

What are you trying to do with the formula?

Post a screen shot using one of the Add-Ons in my signature.

 

[ukmikeb]

If K6 is 01/11/2015 and AU3 is 1 then the result is 01/11/2015.

What cell is the formula in?

What are you trying to do with the formula?

Thank you for your PM and the link to your source file - test.

Your answers to the above should have been :-
1, K8
2, I am trying to mirror the months in a vertical format rather than 3 months horizontally with the exception that there is a blank cell between each day.

I have created a formula which replicates January in the new format In K8 :-

=IF(N(MOD(COLUMNS($K8:K8),2)),INDEX('2016'!$A$6:$W$40,MATCH($K$6,'2016'!$A$6:$W$6,0)+2+ROWS($K9:$K$9)-1,INT(COLUMNS($K8:K8)/2)+1),"")

dragged across and down.

I will get back to the rest when I have completed some necessary tasks.

 

[Roo007]

Thank you! FYI columns A-J will not have formulas in the cell, only text.

 

[ukmikeb]

Rather than make specific refernce to cell addresses each of the following formulae will be placed in the leftmost cell under "Domingo" for the month in question, dragged across to the column headed "Sabado" and yhen dragged down four or five rows in order to complete the days for that month.

January

=IF(N(MOD(COLUMNS($K$8:K$8),2)),INDEX('2016'!$A$6:$W$40,MATCH($K$6,'2016'!$A$6:$W$6,0)+2+ROWS($K$8:$K8)-1,INT(COLUMNS($K$8:K$8)/2)+MATCH($K$6,'2016'!$A$6:$W$6,0)),"")

February

=IF(N(MOD(COLUMNS($K$8:K$8),2)),INDEX('2016'!$A$6:$W$40,MATCH($K$6,'2016'!$A$6:$W$6,0)+2+ROWS($K$17:$K17)-1,INT(COLUMNS($K$8:K$8)/2)+MATCH($K$15,'2016'!$A$6:$W$6,0)),"")

March

=IF(N(MOD(COLUMNS($K$8:K$8),2)),INDEX('2016'!$A$6:$W$40,MATCH($K$6,'2016'!$A$6:$W$6,0)+2+ROWS($K$26:$K26)-1,INT(COLUMNS($K$8:K$8)/2)+MATCH($K$24,'2016'!$A$6:$W$6,0)),"")

April

=IF(N(MOD(COLUMNS($K$8:K$8),2)),INDEX('2016'!$A$15:$W$40,MATCH($K$33,'2016'!$A$15:$W$15,0)+2+ROWS($K$35:$K35)-1,INT(COLUMNS($K$8:K$8)/2)+MATCH($K$33,'2016'!$A$15:$W$15,0)),"")

May

=IF(N(MOD(COLUMNS($K$8:K$8),2)),INDEX('2016'!$A$15:$W$40,MATCH($K$33,'2016'!$A$15:$W$15,0)+2+ROWS($K$44:$K44)-1,INT(COLUMNS($K$8:K$8)/2)+MATCH($K$42,'2016'!$A$15:$W$15,0)),"")

June

=IF(N(MOD(COLUMNS($K$8:K$8),2)),INDEX('2016'!$A$15:$W$40,MATCH($K$33,'2016'!$A$15:$W$15,0)+2+ROWS($K$53:$K53)-1,INT(COLUMNS($K$8:K$8)/2)+MATCH($K$51,'2016'!$A$15:$W$15,0)),"")

July

=IF(N(MOD(COLUMNS($K$8:K$8),2)),INDEX('2016'!$A$24:$W$40,MATCH($K$60,'2016'!$A$24:$W$24,0)+2+ROWS($K$62:$K62)-1,INT(COLUMNS($K$8:K$8)/2)+MATCH($K$60,'2016'!$A$24:$W$24,0)),"")

August

=IF(N(MOD(COLUMNS($K$8:K$8),2)),INDEX('2016'!$A$24:$W$40,MATCH($K$60,'2016'!$A$24:$W$24,0)+2+ROWS($K$71:$K71)-1,INT(COLUMNS($K$8:K$8)/2)+MATCH($K$69,'2016'!$A$24:$W$24,0)),"")

September

=IF(N(MOD(COLUMNS($K$8:K$8),2)),INDEX('2016'!$A$24:$W$40,MATCH($K$60,'2016'!$A$24:$W$24,0)+2+ROWS($K$80:$K80)-1,INT(COLUMNS($K$8:K$8)/2)+MATCH($K$78,'2016'!$A$24:$W$24,0)),"")

October

=IF(N(MOD(COLUMNS($K$8:K$8),2)),INDEX('2016'!$A$33:$W$40,MATCH($K$87,'2016'!$A$33:$W$33,0)+2+ROWS($K$89:$K89)-1,INT(COLUMNS($K$8:K$8)/2)+MATCH($K$87,'2016'!$A$33:$W$33,0)),"")

November

=IF(N(MOD(COLUMNS($K$8:K$8),2)),INDEX('2016'!$A$33:$W$40,MATCH($K$87,'2016'!$A$33:$W$33,0)+2+ROWS($K$98:$K98)-1,INT(COLUMNS($K$8:K$8)/2)+MATCH($K$96,'2016'!$A$33:$W$33,0)),"")

December

=IF(N(MOD(COLUMNS($K$8:K$8),2)),INDEX('2016'!$A$33:$W$40,MATCH($K$87,'2016'!$A$33:$W$33,0)+2+ROWS($K$107:$K107)-1,INT(COLUMNS($K$8:K$8)/2)+MATCH($K$105,'2016'!$A$33:$W$33,0)),"")

hth

 

[ukmikeb]

I was also able to modify the original formula to cope with the column spacing.

For January :-

=IF(MOD(COLUMNS($K$6:K$6),2),IF(MONTH($K$6)<>MONTH($K$6-(WEEKDAY($K$6,1)-($AU$3-1))-IF((WEEKDAY($K$6,1)-($AU$3-1))<=0,7,0)+(ROWS($K$8:$K8)-1)*7+(INT(COLUMNS($K$6:K$6)/2)+1)),"",$K$6-(WEEKDAY($K$6,1)-($AU$3-1))-IF((WEEKDAY($K$6,1)-($AU$3-1))<=0,7,0)+(ROWS($K$6:$K6)-1)*7+(INT(COLUMNS($K$6:K$6)/2)+1)),"")

hth

 

[Roo007]

Can you send me the modified file? Thank you!!!! ???

 

[ukmikeb]

Link to file :- https://www.dropbox.com/s/6bzamu4f19xa1bh/RooCalendar.xlsx?dl=0

The formula mentioned in post #8 starts in cell AM8.

Good luck.