# Date Calculations - Occured in Previous 12 Months?

#### The doomed

##### Active Member
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?....
Code:
``````IF((B4>DATE('Stats - Monthly'!\$H\$2-1,FLOOR(MONTH(\$U\$1)-1,3)+1,0))
*
(E4<>'Stats - Monthly'!\$B\$4),
"Prev 12","")``````

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### al_b_cnu

##### Well-known Member
Hi,

Code:
``=IF(AND(B4< DATE(YEAR(TODAY()),MONTH(TODAY()),1),B4 >=DATE(YEAR(TODAY())-1,MONTH(TODAY()),1)),"Prev 12","")``

#### The doomed

##### Active Member
Hi,

Code:
``=IF(AND(B4< DATE(YEAR(TODAY()),MONTH(TODAY()),1),B4 >=DATE(YEAR(TODAY())-1,MONTH(TODAY()),1)),"Prev 12","")``

Hey - thanks for getting back to me.

Looks spot on!

Thank you

Replies
2
Views
295
Replies
5
Views
273
Replies
3
Views
538
Replies
1
Views
191
Replies
17
Views
712

1,195,967
Messages
6,012,600
Members
441,715
Latest member
TTP

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back