Excel Magic Trick 1540: Extract Unique List of Dates From a Date-Time Column

In this video learn how to Extract Unique List of Dates From a Date-Time Column using the SORT, UNIQUE & INT Functions. This is a solution that works in Office 365.

[sasavienne]

No more crazy long array functions needed. Thanks to Unique and Sort. Thanks Mike.

[richardhay]

Super Video! Coming Fast and Furious!! You are MS best salesperson for Office 365 Insider. Who could resist?? For the example in this video I have always used TRUNC but with positive numbers INT or TRUNC will do the trick. Also can subtract the INT (reference) from the number in the cell to get the just the time.

[sachinrv]

Hi Mike, amazing video but for those using earlier versions of office; here is the simplest trick. Assume having written 14/02/2019 9:00:00 AM in cell A1 and want to extract only date i.e. 14/02/2019; then using very useful but not so popular MOD function one can easily achieve that: =A1-MOD(A1, 1).
The result is a whole number after this just press Control+Shift+3 key board shortcut for default date settings you have. Thanks for upload :) :) :). I'm convinced that Office 365 has much much more to offer.

[drsteele]

The spilling technology is really terrific. The one deficiency is that the cell formatting does not spill along with the output.

[ca.lakshminarayanreddyjamb]

Awesome!!! You guys are really amazing, you are making me to learn from each video a new item. Thanks for that.

[sasavienne]

Hi Mike, today I am the first one who watches this video. Best regards..

[HusseinKorish]

Amazing ... that new caculation engine is great .... thanks Mike

[simfinso]

wow.Very useful in many ways in Day to Day life.Inventory Management, Cash Disbursement, Ticket management & Many more.

[ismailismaili]

These are absolutely awesome videos thank u so much Mr.Mike

[wayneedmondson]

Hi Mike.. excellent.. it feels like cheating to be able to do these array gymnastics so easily now. Great tip on using INT(). Just for fun, I did the same against your data, but extracting just the time using MOD().. as in =SORT(UNIQUE(MOD(SalesTable[DateTime], 1))). Job done.. easy! Always great learning and tricks from ExcelIsFun. Thanks and thumbs up!
PS - I agree with DRSteele below.. would be great if the cell formatting of the primary cell flowed to the spilled cells.. or better yet.. a function that you could wrap around the subject formula to replicate the formatting to the spilled cells.. that would give the user a choice to spill the formatting or not.

[SyedMuzammilMahasanShahi]

WoW. Is it Christmas already :P Thanks mike for this amazing video

[alexxgalaxy]

Awesome! What about instead of extracting unique date value, could it be possible to extract the unique year lisylt from a date list?

[khalikshaikh]

Sir how to move criteria in conditonal formatting opp to formatted rows

[rajwinder.zahura]

Can someone please explain how to extract a specific date from a long list of dates list, such as let’s say 15th of every month from the whole year’s dates list? Thanks

[BrandonKennard-mj]

How can I now put that list into a table?
Spill Error

[viewspace]

My excel has no unique, sort and filter formula.
how can i do that?

[ahamsahastramee]

Hey bro..
I'm one of your old subscribers.
I'm really caught up with one silly problem in excel.
Can you please help??
I'd be a great favor..