Group Dates
 

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

Group Dates
 

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" wrote in message
...
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

Group Dates
 

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

"Arvi Laanemets" wrote:

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" wrote in message
...
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

Group Dates
 

Hi Arvi

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

Thanks

"Arvi Laanemets" wrote:

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" wrote in message
...
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