Recently, I was given sales data to be analyzed by date. It was suspected that more sales is done towards the end of month to meet the month end target. Essentially they wanted to know the sales pattern in months across the years to be divided between dates 1-20, last 3 days and remaining days. Different ending date of months made the problem difficult.
The function EOMONTH was there for rescue. It returns the last day of the month that is the indicated number of months before or after start date. It is extensively used in financial world.
I have created dummy data to show the analysis. First I created helper columns to breakdown the problem. Columns were created for first date of the month, 20th date, 3 days before last date and last date.
By giving -1 as months after start date and then adding 1 to it gives me first date of the month. The last date of the month was EOMONTH function with 0 month after start date. Finding 20th date of the month and 3 days before the last date is matter of simple adding and subtracting number.
After making helper columns using simple IF function did the job for me. Then I collated the functions in helper column to make a single cell formula. It did the job for me. Later I used pivot table for further analysis.