Group Dates

[Dave]

Hi,

I would like to be able to group dates into the following categories:

Last Week
Last Month
Last 3 Months
Last 6 Months
Last Year
Over a Year

How do I do this? This needs to work on a weekily basis i.e. to calculate
from the date the sheet is opened.

ANy help would be greatly appreciated.

Thanks

 

[Arvi Laanemets]

Hi

Last Week/Month/Year ? I assume you did mean current one?

Easiest way will be:
Add columns CurrWeek, CurrMonth, Last3Months, Last6Months, CurrYear,
OldOnes, p.e. columns B:G, with your dates in column A.

Depending on your week definition
B2 = ((TODAY()-WEEKDAY(TODAY(),1))<$A2) (US week definition)
or
B2 = ((TODAY()-WEEKDAY(TODAY(),2))<$A2) (European week definition)

C2 = (DATE(YEAR(TODAY()),MONTH(TODAY()),1)<=$A2)

D2 = (DATE(YEAR(TODAY()),MONTH(TODAY())-2,1)<=$A2)

E2 = (DATE(YEAR(TODAY()),MONTH(TODAY())-5,1)<=$A2)

F2 = (YEAR($A2)=YEAR(TODAY()))

G2 = (YEAR(A2)<YEAR(TODAY()))

Copy formulas down for whole your table (I assumed the 1st row will contain
column headers).
Now you can use Autofilter to get wanted set of data displayed.


Arvi Laanemets

 

[Dave]

Thats great!

How can I now count the true statments as a formaula in another sheet so I
would have:

Last Week and then the Number
Last Month etc, etc.

Thanks

 

[Dave]

Hi Arvi

How can I make the formaula give the title "Last Week" instead of the true
statment?

Thanks