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.)
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
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:
=F3-1+0.708333333which equates to Jan-11-2020 17:00 for 01/12/2020. Likewise the End date is:
=F3+0.708333333so 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:
=Raw!$L$2#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.