Highlight week based on 'sign up date' + highlight future dates

irene_ka

New Member
Joined
Oct 13, 2020
Messages
1
Office Version
  1. 365
  2. 2019
Platform
  1. MacOS
Good morning all,

I am hoping you can help me with your Excel expertise and creative brains!

I have a few partners that I have signed up as part of my job, and I am trying to create an 'onboarding' plan for them. So after the sign-up date, I want to send them an email after 14 days, then after 20 days after that, etc.
I created a calendar in Excel with weeks commencing and I am attaching an example below:

Screenshot 2020-10-13 at 08.48.46.png


B6: Partner name
C6: Date of Sign-up
D6: =C6+14 days
E6: =D6-WEEKDAY(D6,2)+1 - this returns the week commencing. So 27/10 would be falling in w/c 26/10
F6: Second phase. =D6+14 days
G6: week commencing for Phase 2

and so on.
The phases vary so it's not a set '14 days' after each other. We also have a lot of different phases, around 10.

QUESTION:
Is there a way for me to put the Sign-Up Date and then the cells H to S to fill automatically based on whether the values in cells H5:S5 match with the dates in cells E6, G6 and so on?
I would like to visually know that on the week commencing 9 November we have to start Phase 2 for partner A, and so on.
The cells would ideally be highlighted in color.


I tried to use the IF MATCH formula but I wouldn't know how to make it work to look up in multiple cells.

Any assistance or suggestions would be highly appreciated.
Thank you very much!

Irene
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Watch MrExcel Video

Forum statistics

Threads
1,122,632
Messages
5,597,287
Members
414,134
Latest member
Tiyas44

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
Top