Formula to Return the Same Day of the Week a Month Later

S

SarasotaSlim

New Member
Joined
May 19, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
I'm trying to write a formula in Excel to return a date that is one month out from another date (supplied by another cell), but is also the same day of the week as the first date.

For example, if I have cell that has the date of 5/19/2022, I want to return 6/23/2022 .

To give some context, I have a schedule to cycle power on equipment or reboot computers at my work. The schedule is based on rebooting things on specific days of the week, so if the equipment in question is rebooted today, 5/19/2022 - a Thursday - and the schedule is to reboot it every month, I want to reboot it again one month away, but still on a Thursday.

This is the formula a colleague of mine wrote, which includes other timeframe variables such as Quarterly, Bi-Monthly, and Semi-Yearly.

The Weekly seems to be working, but the others don't return the correct day of the week.

Any help is appreciated!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
What is the criteria you are using to require the new date to be 6/23/2022 and not 6/16/2022 ?
 
Upvote 0
That criteria isn't as important. That was just the same week of the month (the third full week). It could just as easily be 6/16. Either way, the formula my colleague wrote is returning 6/13/2022, which is a Monday, not a Thursday.
 
Upvote 0
You could always add 28 days to the date (that would give you the 6/16/2022 for your example).

=A1+28
 
Upvote 0
Seriously, not a bad idea. Sometimes simple is the way to go. I'll try it. Thanks!
 
Upvote 0
So, now that I'm thinking about this some more, that solution only works if the reboot was done on the correct day of the week, which doesn't always happen. If I'm supposed to do a reboot on a Monday, but I don't have time and do it Tuesday, adding 28 will give me the Tuesday of the week I need to do it the next month instead of the Monday.

Again, any help is greatly appreciated.
 
Upvote 0
I am not sure I understand the problem. The date you want to calculate next month from is in a cell, right? You can add 28 to that date on any day you like and it will still come out 4 weeks later. What am I missing in the problem you are trying to describe?
 
Upvote 0
So, I have the schedule for Monday through Thursday to reboot certain things on certain days.

The cell that the formula is referencing is the last time it was rebooted, so it is typed manually by the user when they do the reboot. If something is scheduled for Mondays but doesn't get done until Tuesday, the user-inputted date will be for the Tuesday. Adding 28 to that will give me a Tuesday, but I want it to give me the day of the week it's supposed to be rebooted the next time, which is a Monday.

My colleague may have figured it out using a combination of adding 28 and using the WEEKDAY function, but the formulae are different for different days because of the way the WEEKDAY function works.

This is what he did for Monday:
=IF(C24 = "Weekly", D24 + 7-WEEKDAY(D24, 3), IF(C24 = "Quarterly", (D24+(28*3)) + 7-WEEKDAY(D24+28, 3), IF(C24 = "Monthly", (D24+28) + 7-WEEKDAY(D24+28, 3), IF(C24 = "Bi-Monthly", (D24+(28*2)) + 7-WEEKDAY(D24+28, 3), IF(C24 = "Semi-Yearly", (D24+(28*6)) + 7-WEEKDAY(D24+28, 3), FALSE)))))

And here is Tuesday:
=IF(C20 = "Weekly", D20 + 8-WEEKDAY(D20, 3), IF(C20 = "Quarterly", (D20+(28*3)) + 8-WEEKDAY(D20+28, 3), IF(C20 = "Monthly", (D20+28) + 8-WEEKDAY(D20+28, 3), IF(C20 = "Bi-Monthly", (D20+(28*2)) + 8-WEEKDAY(D20+28, 3), IF(C20 = "Semi-Yearly", (D20+(28*6)) + 8-WEEKDAY(D20+28, 3), FALSE)))))
 
Upvote 0
SarasotaSlim said:
So, I have the schedule for Monday through Thursday to reboot certain things on certain days.

The cell that the formula is referencing is the last time it was rebooted, so it is typed manually by the user when they do the reboot. If something is scheduled for Mondays but doesn't get done until Tuesday, the user-inputted date will be for the Tuesday. Adding 28 to that will give me a Tuesday, but I want it to give me the day of the week it's supposed to be rebooted the next time, which is a Monday.
Click to expand...
So are you saying the Monday date does not exist anywhere in your data??? Only the date the user enters??? I think if you posted your layout for us, that might make things clearer. Here is something I have posted in the past that should give you an idea of the problem from my side of the discussion...

Please Note
-------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data). To sum up... we only know what you tell us, nothing more.
 
Upvote 0
Thanks for the tips... I'm attaching a screenshot, in addition to mini-sheets.

I've gotten a lot of it to work, but maybe there's a better way.

Again, thank you for your time and patience.


Monday:
Excel Test.xlsx
ABCDEFG
1SystemTimeFrequencyLast RebootInitialsNext RebootNotes
2MONDAY
3System Group 1
4SYSTEM 19:00 AMQuarterly2/21/2022DL5/23/2022
5System Group 2
6SYSTEM 29:00 AMWeekly5/16/2022DL5/23/2022
7SYSTEM 31:00 PMMonthly5/23/2022DL6/27/2022
8SYSTEM 49:00 AMBi-Monthly5/16/2022DL7/18/2022
9SYSTEM 59:00 AMQuarterly5/2/2022DL8/1/2022
10SYSTEM 69:00 AMSemi-Yearly1/17/2022DL7/11/2022
Monday
Cell Formulas
RangeFormula
F4,F6:F10F4=IF(C4 = "Weekly", D4 + 7-WEEKDAY(D4, 3), IF(C4 = "Quarterly", (D4+(28*3)) + 7-WEEKDAY(D4+28, 3), IF(C4 = "Monthly", (D4+28) + 7-WEEKDAY(D4+28, 3), IF(C4 = "Bi-Monthly", (D4+(28*2)) + 7-WEEKDAY(D4+28, 3), IF(C4 = "Semi-Yearly", (D4+(28*6)) + 7-WEEKDAY(D4+28, 3), FALSE)))))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C:FExpression=$C1 = "As Needed"textNO
C:FExpression=$C1 = "Semi-Yearly"textNO
C:FExpression=$C1 = "Quarterly"textNO
C:FExpression=$C1 = "Monthly"textNO
C:FExpression=$C1 = "Bi-Monthly"textNO


Tuesday:
Excel Test.xlsx
ABCDEFG
1SystemTimeFrequencyLast RebootInitialsNext RebootNotes
2TUESDAY
3System Group 1
4SYSTEM 19:00 AMQuarterly2/22/2022DL5/24/2022
5System Group 2
6SYSTEM 29:00 AMWeekly5/17/2022DL5/24/2022
7SYSTEM 31:00 PMMonthly5/24/2022DL6/28/2022
8SYSTEM 49:00 AMBi-Monthly5/17/2022DL7/19/2022
9SYSTEM 59:00 AMQuarterly5/3/2022DL8/2/2022
10SYSTEM 69:00 AMSemi-Yearly1/18/2022DL7/12/2022
Tuesday
Cell Formulas
RangeFormula
F4,F7:F10F4=IF(C4 = "Weekly", D4 + 8-WEEKDAY(D20, 3), IF(C4 = "Quarterly", (D4+(28*3)) + 8-WEEKDAY(D4+28, 3), IF(C4 = "Monthly", (D4+28) + 8-WEEKDAY(D4+28, 3), IF(C4 = "Bi-Monthly", (D4+(28*2)) + 8-WEEKDAY(D4+28, 3), IF(C4 = "Semi-Yearly", (D4+(28*6)) + 8-WEEKDAY(D4+28, 3), FALSE)))))
F6F6=IF(C6 = "Weekly", D6 + 12-WEEKDAY(D22, 3), IF(C6 = "Quarterly", (D6+(28*3)) + 8-WEEKDAY(D6+28, 3), IF(C6 = "Monthly", (D6+28) + 8-WEEKDAY(D6+28, 3), IF(C6 = "Bi-Monthly", (D6+(28*2)) + 8-WEEKDAY(D6+28, 3), IF(C6 = "Semi-Yearly", (D6+(28*6)) + 8-WEEKDAY(D6+28, 3), FALSE)))))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C:FExpression=$C1 = "As Needed"textNO
C:FExpression=$C1 = "Semi-Yearly"textNO
C:FExpression=$C1 = "Quarterly"textNO
C:FExpression=$C1 = "Monthly"textNO
C:FExpression=$C1 = "Bi-Monthly"textNO


Wednesday:
Excel Test.xlsx
ABCDEFG
1SystemTimeFrequencyLast RebootInitialsNext RebootNotes
2WEDNESDAY
3System Group 1
4SYSTEM 19:00 AMQuarterly2/23/2022DL5/18/2022
5System Group 2
6SYSTEM 29:00 AMWeekly5/18/2022DL5/25/2022
7SYSTEM 31:00 PMMonthly5/25/2022DL6/22/2022
8SYSTEM 49:00 AMBi-Monthly5/18/2022DL7/13/2022
9SYSTEM 59:00 AMQuarterly5/4/2022DL7/27/2022
10SYSTEM 69:00 AMSemi-Yearly1/19/2022DL7/6/2022
Wednesday
Cell Formulas
RangeFormula
F4,F6:F10F4=IF(C4 = "Weekly", D4 + 9-WEEKDAY(D4, 3), IF(C4 = "Quarterly", (D4+(28*3)) + 2-WEEKDAY(D4+28, 3), IF(C4 = "Monthly", (D4+28) + 2-WEEKDAY(D4+28, 3), IF(C4 = "Bi-Monthly", (D4+(28*2)) + 2-WEEKDAY(D4+28, 3), IF(C4 = "Semi-Yearly", (D4+(28*6)) + 2-WEEKDAY(D4+28, 3), FALSE)))))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C:FExpression=$C1 = "As Needed"textNO
C:FExpression=$C1 = "Semi-Yearly"textNO
C:FExpression=$C1 = "Quarterly"textNO
C:FExpression=$C1 = "Monthly"textNO
C:FExpression=$C1 = "Bi-Monthly"textNO


Thursday:
Excel Test.xlsx
ABCDEFG
1SystemTimeFrequencyLast RebootInitialsNext RebootNotes
2THURSDAY
3System Group 1
4SYSTEM 19:00 AMQuarterly2/24/2022DL5/26/2022
5System Group 2
6SYSTEM 29:00 AMWeekly5/19/2022DL5/26/2022
7SYSTEM 31:00 PMMonthly5/26/2022DL6/30/2022
8SYSTEM 49:00 AMBi-Monthly5/19/2022DL7/21/2022
9SYSTEM 59:00 AMQuarterly5/5/2022DL8/4/2022
10SYSTEM 69:00 AMSemi-Yearly1/20/2022DL7/14/2022
Thursday
Cell Formulas
RangeFormula
F4,F6:F10F4=IF(C4 = "Weekly", D4 + 10-WEEKDAY(D4, 3), IF(C4 = "Quarterly", (D4+(28*3)) + 10-WEEKDAY(D4+28, 3), IF(C4 = "Monthly", (D4+28) + 10-WEEKDAY(D4+28, 3), IF(C4 = "Bi-Monthly", (D4+(28*2)) + 10-WEEKDAY(D4+28, 3), IF(C4 = "Semi-Yearly", (D4+(28*6)) + 10-WEEKDAY(D4+28, 3), FALSE)))))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C:FExpression=$C1 = "As Needed"textNO
C:FExpression=$C1 = "Semi-Yearly"textNO
C:FExpression=$C1 = "Quarterly"textNO
C:FExpression=$C1 = "Monthly"textNO
C:FExpression=$C1 = "Bi-Monthly"textNO
 

Attachments

  • Excel Screenshot.png
    Excel Screenshot.png
    234.2 KB · Views: 3
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Formula for weekdays of date in another cell
Replies
4
Views
460
Jaxi
J
P
  • Question
Returning a value based on Day & Date
Replies
5
Views
347
felixstraube
F
D
  • Solved
Find a cell in a table, and return the value above
Replies
3
Views
59
Alex Blakenburg
A
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
1K
phiero21
P
C
  • Solved
Formula Help (Calendar) Expanded
2
Replies
15
Views
709
Eric W
Eric W

Forum statistics

Threads
1,214,920
Messages
6,122,262
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top