Auto Sort Range based on current date

andrewb90

Well-known Member
Joined
Dec 16, 2009
Messages
1,077
Hello all,

I have three named ranges on my sheet. (DAYS, MONTHS, & YEARS) They provide values for my combo box userform. What I am trying to do is to have the ranges resorted to match tomorrows date automatically, and I'm not sure how best to accomplish this.
For the MONTH range, since it's April, that would be listed first, then MAY, etc with March at the bottom.
For the DAY as it is the 18th, the 19th would be at the top, then the 20th etc, wrapping around to the 18th being the 31st item.
The Years would be the same way.

Any ideas on how do pull this off?
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Try these 3 formula and use as RefersTo in your named range
If starting formula placed in any row other than row 2, adjust accordingly

in A2 copied down (7 cells - one for each day)
=TEXT(TODAY()+ROW()-2,"DDD")

In B2 copied down (12 cells - one for each month)
=TEXT(EDATE(TODAY(),ROW()-2),"MMM")

In C2 copied down (as many cells as you want
=VALUE(YEAR(TODAY()))+ROW()-2


Named Range Refers to:
DAYS =SheetName!$A$2:$A$8
MONTHS =SheetName!$B$2:$B$13
YEARS =SheetName!$C$2:$C$21 (20 years ahead)
 
Last edited:
Upvote 0
I just discovered one small issue, If the current month is a 30 day month, then 31 will disappear from the list of days. But as these lists fill in a drop down box in my user form, I need to still be able to select 31 should the date selected be a future date. Any ideas on how to accomplish this?

(I'm using"DD" to display day of month as opposed to day of week)
 
Upvote 0
For the DAY as it is the 18th, the 19th would be at the top, then the 20th etc, wrapping around to the 18th being the 31st item.
AND
I need to still be able to select 31 should the date selected be a future date

2 consecutive months with 31 days required to make this work - December and January fit the bill
The range for the dropdown in combobox is A2:A32

formula in A1
=(DATE(2018,12,VALUE(TEXT(TODAY(),"dd"))))
converts today's day to the same day of the month in December

formula in A2 copied down as far as A32

=VALUE(TEXT(A1+1,"dd"))
returns all numbers from 1 to 31

:warning: insert check within the userform code to ensure combo date is a valid date
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top