Group Dates

  • Thread starter Thread starter Dave
  • Start date Start date
D

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
 
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
 
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
 
Hi Arvi

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

Thanks
 
Back
Top