Ask a new question

Excel select dates by mmdd and sort

Hi

I have a large ws with information about contacts - address, phone, etc. One of the columns is birthdate, in the form 8/24/1986.

However, there are many blanks (eg Business without this info); and many with a date of the form 7/22/1900 - the 1900 is because I only know the month and day of birth, but not the year. Others have full day-month-year birthdates.

I would like to extract the known birthdays from the ws and sort them by month and day, ignoring the year. I tried a pivot table with calculated field (didn't work). Another solution was a formula in 2 cols of another ws:

for the birthday:

=IF(main!AC2="","",main!AC2) ;;and I formatted the cell as 01-Jan

and the next column for the first and last names as:

=IF(A2="","",CONCATENATE(main!A2,"  ",main!C2))

 

This partially works, but it sorts by the 'invisible year' of the date, yielding Jan - Dec cycles by year.

Any suggestions!

 

Thanks in advance

I would think you could use a helper column with this formula copied down in it...

=IF(YEAR(A1)=1900,"",TEXT(A1,"mmdd"))

and then sort on that helper column.

*** PLEASE READ ***

If this response answers your question, please mark it as the "Answer".

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

There are many ways to do this but you might start by applying a filter to the date column (Data, Filter), open the filter and exclude the 1900 dates.  Then if all you want are the dates select them and copy them to another sheet.  If you want more, select more.

On the new sheet, assume the dates start in A1 with a title.  In an emply column say D, enter the following formula (in D2):

=TEXT(A2,"m-d")

in E2 concatinate the first and last names:

=B2&" "&C2

Sort on column D.

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire
Shane Devenshire

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated October 5, 2021 Views 682 Applies to: