Remember Me?

#1
December 8th 04, 04:35 PM
 Woodkat Posts: n/a
How can I filter for Birthdays/Anniversaries in Excell?

what if the dates in column A are stored as text in the YYYYMMDD format?

"Bill Martin" wrote:

I have a column of birthdays and would like to be able to filter only those
birthdays that occur in the next 14-21 days so we can mail cards. Is there a
way to do this?

How about this. Assume the birthdays are in column A in date format such as
9/23/1950. Then create a second column B where

B1 = if(mod(days360(today(),A1),360) < 21,"Send Card!","")

Then just copy that all the way down the B column.

Good luck...

Bill -- (Remove KILLSPAM from my address to use it)

#2
December 8th 04, 04:54 PM
 JulieD Posts: n/a

Hi Woodkat

Something along the lines of
=IF(AND(DATE(2004,MID(A1,5,2),RIGHT(A1,2))TODAY() +14,DATE(2004,MID(A1,5,2),RIGHT(A1,2))<TODAY()+21) ,"card","")

Cheers
JulieD

"Woodkat" wrote in message
...
what if the dates in column A are stored as text in the YYYYMMDD format?

"Bill Martin" wrote:

I have a column of birthdays and would like to be able to filter only
those
birthdays that occur in the next 14-21 days so we can mail cards. Is
there a
way to do this?

How about this. Assume the birthdays are in column A in date format such
as
9/23/1950. Then create a second column B where

B1 = if(mod(days360(today(),A1),360) < 21,"Send Card!","")

Then just copy that all the way down the B column.

Good luck...

Bill -- (Remove KILLSPAM from my address to use it)

#3
December 8th 04, 05:19 PM
 Frank Kabel Posts: n/a

Hi
try to convert them first to a real Excel date. e.g. with the formula
=--TEXT(A1,"0000-00-00")
format this cell as date and copy for all rows. Now use this helper
column

--
Regards
Frank Kabel
Frankfurt, Germany

"Woodkat" schrieb im Newsbeitrag
...
what if the dates in column A are stored as text in the YYYYMMDD

format?

"Bill Martin" wrote:

I have a column of birthdays and would like to be able to filter

only those
birthdays that occur in the next 14-21 days so we can mail cards.

Is there a
way to do this?

How about this. Assume the birthdays are in column A in date

format such as
9/23/1950. Then create a second column B where

B1 = if(mod(days360(today(),A1),360) < 21,"Send Card!","")

Then just copy that all the way down the B column.

Good luck...

Bill -- (Remove KILLSPAM from my address to use it)

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post bkbrueggemann Excel Discussion (Misc queries) 3 February 1st 05 11:49 PM LucianoG Excel Discussion (Misc queries) 1 December 6th 04 08:38 PM b1 Excel Discussion (Misc queries) 1 December 3rd 04 03:20 AM rexmann Excel Discussion (Misc queries) 2 November 30th 04 02:11 PM Gabriel20783 Excel Discussion (Misc queries) 1 November 29th 04 08:55 PM

All times are GMT +1. The time now is 08:11 PM.