Format a date in shortest format possible indicating how many days have passed (Past date)
If date1-date2 < 8 Sat, Fri, …
If date1-date2 < 350 Dec24, Jan4, Aug14
Else 2020Feb4
Category: AND
Return 12 values in one number
A technic I loved using, return list of items (up to 12 items now) in ONE number.
Let us say you have list of 12 items, and you want a function that will return some of that list (with no order), then this is how you can do it. This has been sitting around for awhile in my archive. I called it 12Return4095
Cells with additional spaces
Detect which cells has additional spaces added by mistake by user.
=IF(AND(Left(F2, 2) = "No", LEN(F2) >2, "Space at end", "")
Highlight dates dynamically
Conditional formatting is another powerful feature in Excel, especially when you combine it with functions
A simple function as in below if you set it up inside Conditional formatting, can do magic
IF with AND and IF with OR
The powerful function If can already do a lot of tricks, but we can for sure do more when we use it with AND or OR functions.
Also, once we understand that, we can use the power of combining AND and OR inside the Logical test of IF, to make it even smarter
Once good example is when we need to see if a certain date is within the same month as this month like below…