Chris00000
New Member
- Joined
- Jul 2, 2021
- Messages
- 4
- Office Version
- 2016
- Platform
- Windows
I'm trying to pull quarter-to-date figures from a model that doesn't have dates but rather has months in Jan Feb Mar (etc) format. A2 is my reference date, which is simply a TODAY() formula. Column O is the values, M is the numeric values I inserted next to Jan Feb Mar, etc. My issue is that my current month is July, so the alternative IF statement should work, but it seems it's only pulling the first IF result. Even if I use 0 in the alternative, the first IF result is pulled.
=IF(OR(VALUE(MONTH($A$2))<>1,VALUE(MONTH($A$2))<>4,VALUE(MONTH($A$2))<>7,VALUE(MONTH($A$2))<>10),SUM(INDEX($O$8:$O$19,MATCH(VALUE(MONTH(EOMONTH($A$2,MOD(-MONTH($A$2),3)-3))+1),$M$8:$M$19,0)):OFFSET(INDEX($O$8:$O$19,MATCH(VALUE(MONTH($A$2)),$M$8:$M$19,0)),-1,0))/1000+INDEX($O$8:$O$19,MATCH(VALUE(MONTH($A$2)),$M$8:$M$19,0))/1000*(($A$2-EOMONTH($A$2,-1))/(EOMONTH($A$2,0)-EOMONTH($A$2,-1))),0)
=IF(OR(VALUE(MONTH($A$2))<>1,VALUE(MONTH($A$2))<>4,VALUE(MONTH($A$2))<>7,VALUE(MONTH($A$2))<>10),SUM(INDEX($O$8:$O$19,MATCH(VALUE(MONTH(EOMONTH($A$2,MOD(-MONTH($A$2),3)-3))+1),$M$8:$M$19,0)):OFFSET(INDEX($O$8:$O$19,MATCH(VALUE(MONTH($A$2)),$M$8:$M$19,0)),-1,0))/1000+INDEX($O$8:$O$19,MATCH(VALUE(MONTH($A$2)),$M$8:$M$19,0))/1000*(($A$2-EOMONTH($A$2,-1))/(EOMONTH($A$2,0)-EOMONTH($A$2,-1))),0)