excel_intermediate_22
New Member
- Joined
- Jul 23, 2010
- Messages
- 6
"{=IF(MONTH(DATE(YEAR(AA3),MONTH(AA3),1))<>MONTH(DATE(YEAR(AA3),MONTH(AA3),1)-(WEEKDAY(DATE(YEAR(AA3),MONTH(AA3),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"",DATE(YEAR(AA3),MONTH(AA3),1)-(WEEKDAY(DATE(YEAR(AA3),MONTH(AA3),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)}"
Hi Guys, i am an intermediate on excel. However recently came across this formula, could you please explain what the {0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1) part is doing here. I havent come across this before.
AA3 is a reference which looks up a static 1st of the month date (01/07/2010) which i populate. The clever thing about this formula is as i change the month this formula is the same in all 30 cells of a grid, however the values are different, it populates that months dates
Could anyone help here.
Thanks
Will
Hi Guys, i am an intermediate on excel. However recently came across this formula, could you please explain what the {0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1) part is doing here. I havent come across this before.
AA3 is a reference which looks up a static 1st of the month date (01/07/2010) which i populate. The clever thing about this formula is as i change the month this formula is the same in all 30 cells of a grid, however the values are different, it populates that months dates
Could anyone help here.
Thanks
Will