Question description:
This user has given permission to use the problem statement for this
blog.
I am curious if there is a way to input a start date and have excel notify monthly. I am working with monthly check ins and would like to know who needs a report. I saw that I could type in a date and have it notify me of that certain date or prior to, but I didn't see reoccurring.
Solved by A. H. in 44 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
23/04/2018 - 07:27
Hi…Welcome to Got It Pro
Excelchat Expert
23/04/2018 - 07:27
In what form, are you looking the notification to happen?
User
23/04/2018 - 07:28
if it could highlight the cell, that would work
Excelchat Expert
23/04/2018 - 07:28
ok
Excelchat Expert
23/04/2018 - 07:29
and will notification be on a specific day of the month?
Excelchat Expert
23/04/2018 - 07:29
say every 1st of the month
User
23/04/2018 - 07:30
yeah. for example, if check ins were implemented today, they would be required the 23rd of every month for the next 6 months
Excelchat Expert
23/04/2018 - 07:31
ok, 23rd of every month
User
23/04/2018 - 07:32
yes, that works
Excelchat Expert
23/04/2018 - 07:32
ok got you
Excelchat Expert
23/04/2018 - 07:35
[Uploaded an Excel file]
Excelchat Expert
23/04/2018 - 07:36
cell B2 is highlighted based on cell A1
Excelchat Expert
23/04/2018 - 07:36
just to tell you how it works, i have entered 24th Jan, 2018 in cell A3 and it highlights cll B3
Excelchat Expert
23/04/2018 - 07:37
Is that what you wanted?
Excelchat Expert
23/04/2018 - 07:37
and this will notify every month yb highligting the cell, when the date arrives
Excelchat Expert
23/04/2018 - 07:39
Hi...are you there?
User
23/04/2018 - 07:40
When I tried to change the date in A1, just to test it, it just repeated the date in the B1
Excelchat Expert
23/04/2018 - 07:40
what date you are putting in A1?
User
23/04/2018 - 07:40
oh actualy it just did 3 months way
User
23/04/2018 - 07:41
I tried to type in 5/24/2018, to see if it would change it to 6/24/2018
User
23/04/2018 - 07:41
and it changed it to 4/24/2018
User
23/04/2018 - 07:42
what I was hoping for was if I typed in a date, it would auto fill for one month away and highlight when that date (the future date) came up.
Excelchat Expert
23/04/2018 - 07:43
ok ...so if you putting a future date, then I will need to change the formula
Excelchat Expert
23/04/2018 - 07:43
let me change it
User
23/04/2018 - 07:43
I hope that makes sense
Excelchat Expert
23/04/2018 - 07:44
yes, it does
Excelchat Expert
23/04/2018 - 07:45
ok so it should be 24th june in cell B1
Excelchat Expert
23/04/2018 - 07:45
now when do you want that highlighted?
User
23/04/2018 - 07:46
either the day of or one day prior. doesn't matter
Excelchat Expert
23/04/2018 - 07:48
ok
Excelchat Expert
23/04/2018 - 07:50
something like this
[Uploaded an Excel file]
User
23/04/2018 - 07:51
yes! that works!
Excelchat Expert
23/04/2018 - 07:52
and it will highlight only when the date is either today or a day prior
Excelchat Expert
23/04/2018 - 07:52
Is there anything else I can help you with this query?
Excelchat Expert
23/04/2018 - 07:53
highlight is done through conditional formatting formula
User
23/04/2018 - 07:54
and if I wanted to make happen for another row I would just change all the A1's to whatever cell I wanted?
Excelchat Expert
23/04/2018 - 07:55
yes, you will just need to drag down the formula in cell B1
Excelchat Expert
23/04/2018 - 07:55
and then you can put your date in any row in column A
User
23/04/2018 - 07:55
how do I drag it down?
Excelchat Expert
23/04/2018 - 07:57
when you click on cell B1, and hover to the lower right corner, you will see a + symbol appear, that's when you can drag it down and you formula will be copied
Excelchat Expert
23/04/2018 - 07:58
Alternatively, you can just copy cell B1 and paste it below
Excelchat Expert
23/04/2018 - 07:59
but since you want to copy the formula down, in order for the conditional format to also copy, we will need to change the conditional format formula to take cell relative reference instead of the absolute reference.
Excelchat Expert
23/04/2018 - 07:59
Are you familiar with conditional formatting with formulas?
User
23/04/2018 - 07:59
i am not
Excelchat Expert
23/04/2018 - 07:59
Should I guide you as how to do that?
User
23/04/2018 - 08:00
that would be helpful
Excelchat Expert
23/04/2018 - 08:00
ok perfect
Excelchat Expert
23/04/2018 - 08:00
Please click on cell B1
Excelchat Expert
23/04/2018 - 08:01
And then form Home tab, go to Conditional Formatting
Excelchat Expert
23/04/2018 - 08:01
pull down conditional formatting menu and you will find Manage rules, Click on that
Excelchat Expert
23/04/2018 - 08:02
oh..i think you are clicking on this google sheet
Excelchat Expert
23/04/2018 - 08:02
Please follow me on the excel file I sent you.
User
23/04/2018 - 08:02
will do
Excelchat Expert
23/04/2018 - 08:03
So do you Conditional formatting rule manager dialog box opened now?
User
23/04/2018 - 08:03
yep!
Excelchat Expert
23/04/2018 - 08:03
you will see the conditional formatting rule I just applied
Excelchat Expert
23/04/2018 - 08:04
So click on Edit rule
Excelchat Expert
23/04/2018 - 08:04
Now you will see a formula which is driving this condition
Excelchat Expert
23/04/2018 - 08:04
=OR($B$1=TODAY(),TODAY()-$B$1=1)
Excelchat Expert
23/04/2018 - 08:05
$B$1 -----as you notice it has a $ sign before column and row, which means its an absolute reference
Excelchat Expert
23/04/2018 - 08:06
and this will always consider cell B1 for conditional format, even if you copy this format to the cell below
Excelchat Expert
23/04/2018 - 08:06
Now we need it to refer cell B2, if we want conditional format for cell B2
Excelchat Expert
23/04/2018 - 08:07
So instead of $B$1, you will have to change it to $B1
Excelchat Expert
23/04/2018 - 08:07
So your formula will become
Excelchat Expert
23/04/2018 - 08:07
=OR($B1=TODAY(),TODAY()-$B1=1)
Excelchat Expert
23/04/2018 - 08:07
Are you with me?
User
23/04/2018 - 08:07
yes
Excelchat Expert
23/04/2018 - 08:08
ok..replace that formula with the one I just gave you
Excelchat Expert
23/04/2018 - 08:08
and click OK
Excelchat Expert
23/04/2018 - 08:08
Now you can copy cell B1, to anywhere below and it will copy cell formula as well as conditional formatting appropriately
User
23/04/2018 - 08:09
okay!
Excelchat Expert
23/04/2018 - 08:10
Does that solve your query?
User
23/04/2018 - 08:11
yes!
Excelchat Expert
23/04/2018 - 08:11
Please do give me a good feedback for my service.
User
23/04/2018 - 08:11
thanl you
Excelchat Expert
23/04/2018 - 08:11
Thanks for your time. Please do come back for any new question. Have a great day ahead!
User
23/04/2018 - 08:11
you too!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
