How to count the last 30 days of activities

[MSSailor]

Have several columns with details
Column A5 and down=Date
Column B5 and down=Activity
Column C5 and down=Type of transportation

I now have 250 rows with activities from today and back in time.
My question is how can I have a variable in A1 where I can ask the last X
days activities. I will choose in A1 how many days back in time the number of
activities done.

 

[Ed Ferrero]

Hi MSSailor,

Have several columns with details
Column A5 and down=Date
Column B5 and down=Activity
Column C5 and down=Type of transportation

I now have 250 rows with activities from today and back in time.
My question is how can I have a variable in A1 where I can ask the last X
days activities. I will choose in A1 how many days back in time the number
of
activities done.

Put this formula in cell D5
=INT(NOW())-A5<=$A$1

This returns TRUE if the date in A5 is A1 days or less than today.

Filter for column D is TRUE

Ed Ferrero

 

[MSSailor]

You wrote to put it in D5=INT(NOW())-A5<=$A$1
Can the value in D5 be the value that I can fill in.
If D15=30 ; is calculating numbers of activities 30 days back in time
starting from today
If D15=15 ; is calculating numbers of activities 30 days back in time
starting from today


Column A Column B
DATE ACTIVITY
A5=date 1 B5=Running
A6=date 2 B6=Jogging
A7=date 3 B7=Diving
An
A50 date 46 B46=Skiing

 

[David Biddulph]

Is there any advantage in using INT(NOW()) instead of TODAY() ?

 

[Ed Ferrero]

David Biddulph wrote,

Is there any advantage in using INT(NOW()) instead of TODAY() ?

No, old habit

Ed Ferrero
www.edferrero.com

 

[Ed Ferrero]

Hi MSSailor,

You wrote to put it in D5=INT(NOW())-A5<=$A$1
Can the value in D5 be the value that I can fill in.
If D15=30 ; is calculating numbers of activities 30 days back in time
starting from today
If D15=15 ; is calculating numbers of activities 30 days back in time
starting from today


Column A Column B
DATE ACTIVITY
A5=date 1 B5=Running
A6=date 2 B6=Jogging
A7=date 3 B7=Diving
An
A50 date 46 B46=Skiing

I mean put =INT(NOW())-A5<=$A$1 in cell D5
Then D5 will evaluate to TRUE or FALSE depending on the values in cells A1
and A5

If A1 = 30 (meaning you want the last 30 days activities)
And A5 = 12-3-2009
Then D5 will show TRUE

If A5 = 12-1-2009
Then D5 will show FALSE

Copy cell D5 down as far as you need to

Then use autofilter to show all the rows where column D = TRUE

As David Biddulph pointed out, you can simplify the formula to
=TODAY()-A5<=$A$1

Ed Ferrero
www.edferrero.com