Forum Discussion
Sorting and Formatting Dates Formula
Hi! I am pulling data from one sheet (Current Employees) in a workbook to a new sheet (Anniversaries). I want to sort a column of dates by month and day. The original data is in a m/d/yyyy format (examples- 1/1/2000 and 12/22/2000). I have to use a formula because you can't sort an array with the sort/filter functions when you pull data from another sheet.
My current formula is below. I used the SORT formula (with the array as an INDEX formula to pull only certain columns from the sheet. The sort index is the column that has the dates. As is, the dates are ordered chronologically (by year, then month, then day). I want to basically ignore the year and have it sorted by month and day. Can I do this without adding columns to my original sheet (Current Employees)?
=SORT(INDEX('Current Employees'!A2:AD97,SEQUENCE(ROWS('Current Employees'!A2:AD97)),{1,2,3,5,6,17,28,29,30}),7)
This interprets the data as the date of birth and then sorts by birthday order within the coming year.
= LET( dob, EmployeeTbl[DoB], birthday, DATE(2024, MONTH(dob), DAY(dob)), requiredColumns, CHOOSECOLS(EmployeeTbl, {1,2,3,5,6,17,28,29,30}), SORTBY(requiredColumns, birthday) )Not sure what the resistance to adding columns is, but if you will recall, dates in Excel are actually serial numbers. So the sorting isn't actually done by year, month and day anyway. It's done by serial number. If you want to ignore the year, you're going to have to extract month and day from the serial number into "helper columns" and then do the sorting.
That works, but when I inserted a column, it changed the formatting for another column. It is a date in the original sheet (and it's being used for calculations in another column), but now, on the sheets where I am extracting the array, it shows the serial numbers. Is there a way to lock the formatting for that column so the dates show as dates on the other sheets?
Just reset the format to the desired date format on the sheet where you're extracting. What's extracted is the serial number; it's up to you to format as desired.
