Date near end of month checkbox

gjohnson.pcpro

Hello all!

I have a sheet that contains a column titled Billing Date. This column is used to run revenue reports by month. I would like to add a column to my sheet with a checkbox that is only checked if the Billing Date is near the end of the month. Say, within 3 days of the end of the month. We'll call this new column End of Month.

I'll use May as an example. There are 31 days in May. So each line with a Billing Date of 5/29, 5/30 and 5/31 would all have a check mark in the column End of Month.

I've done a bit of searching and can't seem to find a formula that will accomplish this. Any assistance would be appreciated.

Thank you!

Heather_Verde

I feel like you need a helper column or some sort to identify the month end date (either by formula or a lookup value off another sheet). And then base you checkbox formula off the difference between billing date and month end date.

Scott Peters

Hello @gjohnson.pcpro - There are many ways you could solve this. I'll provide the long way of thinking through it, but you could combine a few of these columns by compounding the formulas. Here I am showing an example using a billing date of 5/31, where today's date is 5/2/23, and the next row if today was your trigger date of 5/28/23:

You could add onto this using one of the Networkday formulas, and you could also add exceptions for national holidays or your own company holidays. I hope this is helpful!

gjohnson.pcpro

Thank you so much for your help, Scott! I was able to figure it out using your formulas. Can't thank you enough for taking the time. Really appreciate it.

gjohnson.pcpro

For anyone who might find this thread in the future:

I created a new column using Scott's "This Day" formula above. This column contains =DAY from my Billing Date. Then I used an automation workflow that automatically checks the box in my "End of Month" column if the day from the billing date is greater than 25. A little workaround for anyone who doesn't quite grasp the complexity of spreadsheet formulas. :)