Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 Filter Problem | Excel Discussion (Misc queries) | |||
advanced filter won't allow me to filter on bracketed text (-456.2 | Excel Discussion (Misc queries) | |||
Filter changes upon reopening file! | Excel Discussion (Misc queries) | |||
filter and count | Excel Discussion (Misc queries) | |||
EXCELL 2002 Glitch???? | Excel Discussion (Misc queries) |