Need to identify the current month plus the last 7 days of the prior month

Hi there,

I have a helper column that checks the "Current Month" check box if the date is within the current month:

I used this formula:

=IF(MONTH(Date@row) = MONTH(TODAY()), 1)

The business is now asking to identify when a date is in the current month or in the last 7 days of the previous month. For example, this month the check marks would appear in the "Current + 7 prior" column in rows with dates in December and November 24-30. Next month, the check marks would be on dates within January as well as December 25-31.

I am completely stumped on how to do this and welcome any thoughts.

Thank you!

Find more posts tagged with

Sheets

Formulas

Accepted answers

KPH

Step 1 - Find the last 7 days in the previous month

This formula creates a date that is the first day of the current month and year:

=(DATE(YEAR(TODAY()), MONTH(TODAY()), 1))

Subtracting 8 gives you the date at the start of the 7 days prior to previous month end:

=(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 8)

Step 2 - Include this date in the IF formula for the checkbox

This formula will evaluate if the date in the date column is after the date 7 days before the end of the current month and check the box if it is:

=IF(Date@row >= (DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 8), 1)

Step 3 - See if the date is within the current month

To prevent the box being checked for rows where the date is beyond the end of the current month, we need to add an end date (the current month end) as well.

The formula will find the first day of the next month and then subtract 1 day to find the last day of the current month

=DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1

It will however, fail in December (as there is no month after December). So we can wrap it in an IFERROR to return 1st day of 1st month of next year, then subtract 1 to get the last day in December in current year. Like this

=IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1

Step 4 - Check the box if the date is before the end of the current month

This formula, will check the box if the date is before the end of the current month:

=IF(Date@row <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1,1)

Step 5 - Combine both start and end dates into one formula

To check the box only if the date is in the last 7 days of the pervious month and before the last day of the current month, combine the formula in steps 2 and 4 in one IF AND function:

=IF(AND(Date@row >= (DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 8), Date@row <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1), 1)

Here is the result (as of 12/11/23)

All comments

KPH

You need a formula to figure out the last 7 days of the previous month which will vary depending on the length of the month. I did something similar recently in 3 steps. Rather than taking a day at the end of the previous month

1 Find the first day of the current month

2 subtract the 8 days

3 put this in an IF to account for an issue in December/January.

I have a worked example I can share, but also have a meeting to join. I will check back later.

KPH

Step 1 - Find the last 7 days in the previous month

This formula creates a date that is the first day of the current month and year:

=(DATE(YEAR(TODAY()), MONTH(TODAY()), 1))

Subtracting 8 gives you the date at the start of the 7 days prior to previous month end:

=(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 8)

Step 2 - Include this date in the IF formula for the checkbox

This formula will evaluate if the date in the date column is after the date 7 days before the end of the current month and check the box if it is:

=IF(Date@row >= (DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 8), 1)

Step 3 - See if the date is within the current month

To prevent the box being checked for rows where the date is beyond the end of the current month, we need to add an end date (the current month end) as well.

The formula will find the first day of the next month and then subtract 1 day to find the last day of the current month

=DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1

=IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1

Step 4 - Check the box if the date is before the end of the current month

This formula, will check the box if the date is before the end of the current month:

=IF(Date@row <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1,1)

Step 5 - Combine both start and end dates into one formula

To check the box only if the date is in the last 7 days of the pervious month and before the last day of the current month, combine the formula in steps 2 and 4 in one IF AND function:

=IF(AND(Date@row >= (DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 8), Date@row <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1), 1)

Here is the result (as of 12/11/23)

jmhoward

@KPH Thank you so much -- that is amazing! I appreciate that you not only showed the formula, but the logic to get to it. I'm a novice when it comes to formulas, so the step-by-step walk-through was invaluable.

It works like a charm!

I had not used the =< notation in formulas before, so I learned something new! I can thank you enough!

KPH

Happy to help and pleased your found the walk through useful. There are a lot of 1, +1, 1, 1 in that formula and without breaking it down I find it hard to figure out what is doing what. 😁

Now you have your fish and the tools to catch more tomorrow 😉

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.