Sort multiple columns of dates

Hi Experts,

I have a worksheet named "Sorted_Data" which has columns as follows:

Name (A) , Date1 (B), Date2 (C), Date3 (D), Date4 (E), Date5 (F)

I would like to sort the sheet by columns B to F, sending the row with the closest date to today to the top of the list.

I would also like to format the dates with a color if the date is less than 28 days from today.

A header row exists.

Many thanks
Andy
Negativ3Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
Andy,
I array-entered the following formula in cell G2 (copied down) to calculate the minimum number of days between the dates in B2:Fxxx and today:
=MIN(ABS(B2:F2-TODAY()))
To array-enter a formula:
1) Select the cell
2) Click in the formula bar
3) Hold the Control and Shift keys down
4) Hit Enter, then release all three keys
Excel should respond by adding curly braces { } surrounding the formula. If it does not (or if you see #VALUE! error value as the result), repeat steps 1 through 4.

I used the following formula criteria for Conditional Formatting to provide a yellow color if the date were 28 days or less from today:
=ABS(B2-TODAY())<=28

I used the following formula criteria for Conditional Formatting to put a border around the date that is closest to today:
=ABS(B2-TODAY())=$G2                 the $ before $G2 is important!

If you sort on column G in ascending order, you will get the desired results.
DatesClosesToTodayQ28325364.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Negativ3Author Commented:
byundt, that works nicely.

Now, say I want to create the sheet Sorted_Data from a master sheet at the press of a hot-key (ctrl-j). How would I enter the formulas into those cells using the VB editor?
Negativ3Author Commented:
Managed to sort out the VB bit myself. Thanks for the help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.