Wadda_Fett
New Member
- Joined
- Jul 11, 2017
- Messages
- 9
Hello all
I have an automated calendar that changes based on "theyear" in B5, which is currently 2017
Merged cells D5 to J5 have the formula =DATE(theyear,1,1) = January, then L5 to R5 has =DATE(theyear,2,1) = February, T5 to Z5 has =DATE(theyear,3,1) = March
Remaining months are then in D14 to J14, L14 to R14, T14 to Z14 (and then from D23 to J23 and D32 to J32)
The days of the week are in D6 to J6, L6 to R6 (etc under each month) and by selecting all 7 cells (not merged), I then put in {=TRANSPOSE({1;2;3;4;5;6;7})}
This then gives each Day converted to Sun, Mon, Tue etc
The days are then in a block (42 cells) from D7 to J12, L7 to R12, T7 to Z12, D16 to J21 (etc for each month)
Each cell has exactly same formula according to the month.
D7 to J12 has {=IF(MONTH(DATE(YEAR(D5),MONTH(D5),1))<>MONTH(DATE(YEAR(D5),MONTH(D5),1)-(WEEKDAY(DATE(YEAR(D5),MONTH(D5),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"",DATE(YEAR(D5),MONTH(D5),1)-(WEEKDAY(DATE(YEAR(D5),MONTH(D5),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)}
L7 to R12 has {=IF(MONTH(DATE(YEAR(L5),MONTH(L5),1))<>MONTH(DATE(YEAR(L5),MONTH(L5),1)-(WEEKDAY(DATE(YEAR(L5),MONTH(L5),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"",DATE(YEAR(L5),MONTH(L5),1)-(WEEKDAY(DATE(YEAR(L5),MONTH(L5),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)}
So by copying this formula each month - D5 is Jan, L5 is Feb then T5 will be Mar etc....
The result (a little rough example) looks like this:
(D5 to J5) January
(D6)Sun Mon Tue Wed (H6)Thu Fri Sat
(D7) 1 2 3 4 (H7)5 6 7
(D8) 8 9 10 11 (H8)12 13 14
I have a column set up with named list 'Fortnights' of each pay day from AC6 = 12/01/2017, then AC7 = AC6+14 = 26/01/2017, down to A32 which = 11/01/2018.
I wasn't intending on using the column but I may need to as I am getting stuck for ideas.
SO what Im looking for is to have every 2nd Thursday to have a Conditional Format to change colour as a pay day, based on one entry in AC6.
But the formula needs to cover every Thursday column per month, ie: H7 to H12, P7 to P12, X7 to X12, H16 to H21 (etc per month)
This is because the dates obviously change based on the 'theyear' changing and then each cell changing its date and subsequent fortnight.
Eg current dates to highlight would be 12/01/2017, 26/01/2017, 09/02/2017 and 23/02/2017 etc
If that were 2016, then 14/01/2016, 28/01/2016, 11/02/2016 etc
I have tried a simple: If represented selection matches some dates then change colour, however this had to be done about 30 times over about 4 cells at a time,
because the formula were too big and hard to read.
I really hope I don't have to do that again as I am sure there would be an easier way.
Thank you for your time,
I have an automated calendar that changes based on "theyear" in B5, which is currently 2017
Merged cells D5 to J5 have the formula =DATE(theyear,1,1) = January, then L5 to R5 has =DATE(theyear,2,1) = February, T5 to Z5 has =DATE(theyear,3,1) = March
Remaining months are then in D14 to J14, L14 to R14, T14 to Z14 (and then from D23 to J23 and D32 to J32)
The days of the week are in D6 to J6, L6 to R6 (etc under each month) and by selecting all 7 cells (not merged), I then put in {=TRANSPOSE({1;2;3;4;5;6;7})}
This then gives each Day converted to Sun, Mon, Tue etc
The days are then in a block (42 cells) from D7 to J12, L7 to R12, T7 to Z12, D16 to J21 (etc for each month)
Each cell has exactly same formula according to the month.
D7 to J12 has {=IF(MONTH(DATE(YEAR(D5),MONTH(D5),1))<>MONTH(DATE(YEAR(D5),MONTH(D5),1)-(WEEKDAY(DATE(YEAR(D5),MONTH(D5),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"",DATE(YEAR(D5),MONTH(D5),1)-(WEEKDAY(DATE(YEAR(D5),MONTH(D5),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)}
L7 to R12 has {=IF(MONTH(DATE(YEAR(L5),MONTH(L5),1))<>MONTH(DATE(YEAR(L5),MONTH(L5),1)-(WEEKDAY(DATE(YEAR(L5),MONTH(L5),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"",DATE(YEAR(L5),MONTH(L5),1)-(WEEKDAY(DATE(YEAR(L5),MONTH(L5),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)}
So by copying this formula each month - D5 is Jan, L5 is Feb then T5 will be Mar etc....
The result (a little rough example) looks like this:
(D5 to J5) January
(D6)Sun Mon Tue Wed (H6)Thu Fri Sat
(D7) 1 2 3 4 (H7)5 6 7
(D8) 8 9 10 11 (H8)12 13 14
I have a column set up with named list 'Fortnights' of each pay day from AC6 = 12/01/2017, then AC7 = AC6+14 = 26/01/2017, down to A32 which = 11/01/2018.
I wasn't intending on using the column but I may need to as I am getting stuck for ideas.
SO what Im looking for is to have every 2nd Thursday to have a Conditional Format to change colour as a pay day, based on one entry in AC6.
But the formula needs to cover every Thursday column per month, ie: H7 to H12, P7 to P12, X7 to X12, H16 to H21 (etc per month)
This is because the dates obviously change based on the 'theyear' changing and then each cell changing its date and subsequent fortnight.
Eg current dates to highlight would be 12/01/2017, 26/01/2017, 09/02/2017 and 23/02/2017 etc
If that were 2016, then 14/01/2016, 28/01/2016, 11/02/2016 etc
I have tried a simple: If represented selection matches some dates then change colour, however this had to be done about 30 times over about 4 cells at a time,
because the formula were too big and hard to read.
I really hope I don't have to do that again as I am sure there would be an easier way.
Thank you for your time,
