How to subtract a month and have it be the same day of the week of the month.

sgowing

I have a bunch of dates where I would like to subtract one month, but need it to be the same day of the same week of the month. For example, if the date is on the second Thursday of the month, then the result should be one month prior, but also on the second Thursday of the month. However, my dates are all on different days of the month.

Thanks in advance!

Genevieve P.

Hi @sgowing

Alright, I think I've sorted this out by using DAY and dividing by 7. This will help us find what week of the month the date is in, the first, second, third, or fourth. If your date is in the 5th week, we'll leave the cell blank.

I'll break it down for you:

First, we check to see if the Day of the current row is in the 1st week of the month:

(DAY(Date@row) / 7) <= 1

and we then also check to see that if we take that date and minus 28, will that also be in the first week:

(DAY(Date@row - 28) / 7) <= 1)

If this is true, then we minus 28 days:

Date@row - 28

But if not, and if the Date is in the first week, then minus 35.

IF((DAY(Date@row) / 7) <= 1, Date@row - 35,

This completes our first statement:

=IF(AND((DAY(Date@row) / 7) <= 1, (DAY(Date@row - 28) / 7) <= 1), Date@row - 28, IF((DAY(Date@row) / 7) <= 1, Date@row - 35,

Now we have to repeat that exact same logic for the 2nd, 3rd, and 4th weeks, like so:

=IF(AND((DAY(Date@row) / 7) <= 1, (DAY(Date@row - 28) / 7) <= 1), Date@row - 28, IF((DAY(Date@row) / 7) <= 1, Date@row - 35, IF(AND((DAY(Date@row) / 7) <= 2, (DAY(Date@row - 28) / 7) <= 2), Date@row - 28, IF((DAY(Date@row) / 7) <= 2, Date@row - 35, IF(AND((DAY(Date@row) / 7) <= 3, (DAY(Date@row - 28) / 7) <= 3), Date@row - 28, IF((DAY(Date@row) / 7) <= 3, Date@row - 35, IF(AND((DAY(Date@row) / 7) <= 4, (DAY(Date@row - 28) / 7) <= 4), Date@row - 28, IF((DAY(Date@row) / 7) <= 4, Date@row - 35))))))))

Let me know if this give you the desired result!

Cheers,

Genevieve

Bassam Khalil

Hi @sgowing 

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Genevieve P.

Hi @sgowing

Instead of subtracting a month, what about subtracting 4 weeks instead? You can do this by simply using the - sign and the number of days to take away from your date.

Ex:

=Date@row - 28

This will automatically take you 4 weeks earlier, keeping your date on the same day of the week. (See: Use Formulas to Perform Calculations With Dates)

There are some instances where your date may not fall on the previous month though (for example August 31st will take you 4 weeks back to August 3rd).

In this instance, if you need it to be the final week of July instead, you can add in an IF statement to check this.

=IF(MONTH(Date@row - 28) = MONTH(Date@row), Date@row - 35, Date@row - 28)

This says, if the MONTH 4 weeks ago is the same as the MONTH in the date column, then subtract an extra week. Otherwise, just subtract 4 weeks.

Will this work for you?

Cheers,

Genevieve

sgowing

Hi @Genevieve P.! That's close to working, but there are also some months where it wouldn't because of where the week is in the month. For example, 28 days prior to July 6 (first Tues of July) is June 8 (second Tues of June), but is a different month so that formula wouldn't catch it.

Genevieve P.

Hi @sgowing

We can write another logic statement to check for this:

=IF(MONTH(Date@row - 35) = MONTH(Date@row) - 1, Date@row - 35

This says, if you take away 35 days from this date and it's still only in the previous month, then minus 35 days instead of 28. This will ensure that if the date is in the first week of one month it will return the first week of the other month.

Try this:

=IF(MONTH(Date@row - 35) = MONTH(Date@row) - 1, Date@row - 35, IF(MONTH(Date@row - 28) = MONTH(Date@row), Date@row - 35, Date@row - 28))

Keep in mind that this means August 3rd and August 10th will both return July 6th, since July only has 4 Tuesdays but August has 5.

Additionally. June has 5 Wednesdays whereas July only has 4. This means that there is no way to map to June's 5th Wednesday from July - the "Last Wednesday" in July would map to June 23rd. You could add in another statement to see if this is the last week of the month, then only subtract 28 (not 35) but then you would miss out on the second-last week (nothing would map to June 23rd).

Can you identify how you want your weeks to pair up? On weeks where the date column has 5 weeks, do you want to return a Double Week 1 or a Double Week 4?

DATE MONTH has 5wk vs. FORMULA MONTH with 4wk

Week 1 -> Week 1

Week 2 -> Week 2

Week 3 -> Week 3

Week 4 -> Week 4

Week 5 -> Week 4 (duplicate date)

DATE MONTH has 4wk vs. FORMULA MONTH with 5wk

Week 1 -> Week 1

Week 2 -> Week 2

Week 3 -> Week 3

Week 4 -> Week 4

nothing will return -> Week 5

Thanks!

Genevieve

sgowing

There will actually be no dates that start on the fifth day of the month (e.g., no 5th Wednesday). And none of the results should land on the 5th week either. So just need a way for it to always be that the first Monday of one month corresponds to first Monday of the second month, and so on through the fourth Friday of one month is the fourth Friday of the next.

Genevieve P.

Hi @sgowing

Alright, I think I've sorted this out by using DAY and dividing by 7. This will help us find what week of the month the date is in, the first, second, third, or fourth. If your date is in the 5th week, we'll leave the cell blank.

I'll break it down for you:

First, we check to see if the Day of the current row is in the 1st week of the month:

(DAY(Date@row) / 7) <= 1

and we then also check to see that if we take that date and minus 28, will that also be in the first week:

(DAY(Date@row - 28) / 7) <= 1)

If this is true, then we minus 28 days:

Date@row - 28

But if not, and if the Date is in the first week, then minus 35.

IF((DAY(Date@row) / 7) <= 1, Date@row - 35,

This completes our first statement:

=IF(AND((DAY(Date@row) / 7) <= 1, (DAY(Date@row - 28) / 7) <= 1), Date@row - 28, IF((DAY(Date@row) / 7) <= 1, Date@row - 35,

Now we have to repeat that exact same logic for the 2nd, 3rd, and 4th weeks, like so:

=IF(AND((DAY(Date@row) / 7) <= 1, (DAY(Date@row - 28) / 7) <= 1), Date@row - 28, IF((DAY(Date@row) / 7) <= 1, Date@row - 35, IF(AND((DAY(Date@row) / 7) <= 2, (DAY(Date@row - 28) / 7) <= 2), Date@row - 28, IF((DAY(Date@row) / 7) <= 2, Date@row - 35, IF(AND((DAY(Date@row) / 7) <= 3, (DAY(Date@row - 28) / 7) <= 3), Date@row - 28, IF((DAY(Date@row) / 7) <= 3, Date@row - 35, IF(AND((DAY(Date@row) / 7) <= 4, (DAY(Date@row - 28) / 7) <= 4), Date@row - 28, IF((DAY(Date@row) / 7) <= 4, Date@row - 35))))))))

Let me know if this give you the desired result!

Cheers,

Genevieve

sgowing

That looks perfect! Thank you so much!

Genevieve P.

Glad we got it working! 🙂