Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Excel Date Filter Magic


Hello I created the interface as shown on the tutorial on youtube

Sorting Year Month Week as shown

What I wanted to do is go into daily sorting, I am able to sort the specified day with a being date but I would like to end the day at 1700 hour. Im having difficulty

Any Help would be appreciated

Thank Yu



Please see my Answer but if possible edit your question title to read "Filter" (not Fiter)- this may help the searches of other users with similar problems. thanks in advance.
John_Ru (rep: 4937) Feb 25, '23 at 11:01 am
Add to Discussion



Hi and welcome to the Forum 

You seem to be referring to Don's tutorial Excel Date Filter Magic - Last Month, This Month, YTD & More + Awesome Interface.

I'm not clear however what you mean by wanting to "end the day at 1700 hours" but assume this relates to a working day. In the attached file, I've used the file (and data with old dates) from the tutorial and assumed column H of the Raw sheet contains Date and Time values (and have added a random time element to each), setting them to display same).

On the Dashboard sheet, I've added extra (hidden) criteria so "Date" in cell L10 appears as that choice in the Select Period box. Pick that option and conditional formatting reveals a date in F3, currently 01/12/2020 (US style, like Don's tutorial) but formatted so it's clear that it's 12th January 2020 to international users. 

Now the Time portion of column H is represented by the decimal portion of the Date  number and 17:00:00 equates to a decimal 0.708333333'.

Therefore the Start value in M10 is set as 5pm the previous day to taht in cell F3, so:


which equates to Jan-11-2020 17:00 for 01/12/2020. Likewise the End date is:


so Jan-12-2020 17:00. Change the date (say to a day after) and your get times from its previous day after 5pm and the chosen date (up to 5pm).

Revision 26 Feb 2023:

If you have Excel 365 or Excel 2021, you could use the second file below. It adds List data validation to the date cell F3 (which appears when the Period is set to Date via the dropdown) so all the datas in column H of Raw can be selected via its dropdown. The validation formula is:


which points to hidden cell L2 containing the array formula:

=UNIQUE(ROUND(TableRaw[Date and Time],0))

which rounds the date/time values to an integer (date) but removes the duplicates via the Excel 356/ 2021 function UNIQUE.

Hope this helps- if so please remember to mark this Answer as Selected (in accordance with the Rules of the Forum).

If not, please edit your original question to attach a representative Excel file using the Add Files... button to show your data- we should then be able to provide you with an answer.



Hello John Thank You for the response
Yes I made the interface precise to Don's youtube , which is very informative
I see next to the drop down (I m thinking f3 ) does that coincide with the table to the right ?
Makingadifference Feb 25, '23 at 5:59 pm
Yes, F3 is next to the drop down in my file and will be yellow when Date is sekected in the dropdown. (You can restore the formula bar and column headings via the View ribbon to check cell references). Did you change the Period to Last Month say (no data) then look at the Raw data and pick a date to enter when the Period is changed back to Date?

I just added a revision (and second file) which makes it easier to use but only if you have Excel 356 or2021 (your profile currently does NOT say which version you use).

Don't forget the Select the Answer if it helps to solve your problem- thanks!
John_Ru (rep: 4937) Feb 25, '23 at 6:55 pm
Yes I am using 365 for this interface
I will try this out today ,i didnt realize i misspelled Filter I did edit this.
Makingadifference Feb 26, '23 at 9:05 am
Thanks for editting the title. Did you try the second file?
John_Ru (rep: 4937) Feb 27, '23 at 5:35 am
Still no reply? Seems I wasted my time. 
John_Ru (rep: 4937) Mar 6, '23 at 2:19 pm
You're not "making a difference" to this discussion recently! If my solution didn't work for you, please say how.  If it worked, you ought to mark the Answer as Selected (in accordance with the Rules of the Forum) which increases both our reputations here. Over to you... 
John_Ru (rep: 4937) Mar 16, '23 at 10:53 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login