The doomed
Active Member
- Joined
- Feb 13, 2008
- Messages
- 263
Hi folks!
I want to work out if the date in B4 has occured in any of the 12 calendar months prior to the current calendar month.
If it has, I want 'Prev 12' to be displayed. If it did not occur in the time scale I want the cell to be blank, "".
A few things that might help:
B4 is a date formatted as DD/MM/YYYY
D4 is a year function on the date =YEAR(B4)
E4 is a custom month and year function ="M"&(MONTH(B4))&" "&YEAR(B4), i.e. 'M1 2009'
'Stats - Monthly'!$B$4 is the current month and year in the format of E4 (so for today that would be 'M3 2009')
>'Stats - Monthly'!$N$2 contains the current month
>'Stats - Monthly'!$H$2 contains the current year
'Stats - Monthly'!$B$5 is the previous month and year in the format of E4 (so for today that would be 'M2 2009')
>'Stats - Monthly'!$O$2 contains the previous month
>'Stats - Monthly'!$P$2 contains the previous year
Is there something that could easily be done with the following to make it work?....
I want to work out if the date in B4 has occured in any of the 12 calendar months prior to the current calendar month.
If it has, I want 'Prev 12' to be displayed. If it did not occur in the time scale I want the cell to be blank, "".
A few things that might help:
B4 is a date formatted as DD/MM/YYYY
D4 is a year function on the date =YEAR(B4)
E4 is a custom month and year function ="M"&(MONTH(B4))&" "&YEAR(B4), i.e. 'M1 2009'
'Stats - Monthly'!$B$4 is the current month and year in the format of E4 (so for today that would be 'M3 2009')
>'Stats - Monthly'!$N$2 contains the current month
>'Stats - Monthly'!$H$2 contains the current year
'Stats - Monthly'!$B$5 is the previous month and year in the format of E4 (so for today that would be 'M2 2009')
>'Stats - Monthly'!$O$2 contains the previous month
>'Stats - Monthly'!$P$2 contains the previous year
Is there something that could easily be done with the following to make it work?....
Code:
IF((B4>DATE('Stats - Monthly'!$H$2-1,FLOOR(MONTH($U$1)-1,3)+1,0))
*
(E4<>'Stats - Monthly'!$B$4),
"Prev 12","")