HELP! Formulas assistance

Roo007

New Member
Joined
Nov 15, 2015
Messages
26
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

Well-known Member
Joined
Jul 10, 2009
Messages
2,756
Hi

Welcome to the MrExcel Forum.

What is in cell AU3?

What cell is the formula in?

What is the purpose of the formula?
 

ukmikeb

Well-known Member
Joined
Jul 10, 2009
Messages
2,756
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

Well-known Member
Joined
Jul 10, 2009
Messages
2,756
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 :-
Code:
=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

New Member
Joined
Nov 15, 2015
Messages
26
Thank you! FYI columns A-J will not have formulas in the cell, only text.
 

ukmikeb

Well-known Member
Joined
Jul 10, 2009
Messages
2,756
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
Code:
=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
Code:
=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
Code:
=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
Code:
=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
Code:
=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
Code:
=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
Code:
=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
Code:
=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
Code:
=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
Code:
=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
Code:
=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
Code:
=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

Well-known Member
Joined
Jul 10, 2009
Messages
2,756
I was also able to modify the original formula to cope with the column spacing.

For January :-
Code:
=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
 

Forum statistics

Threads
1,082,601
Messages
5,366,571
Members
400,902
Latest member
fathima

Some videos you may like

This Week's Hot Topics

Top