Date Reminder Help Please

W

WarrenMU

New Member
Joined
Nov 5, 2014
Messages
34
Hi All,

Thanks for taking the time to read my thread and help out, I have to admit I am not a pro at this so please be patient. I am on here all the time and can usually find what i need and can revise to make it work but this one im stuck on....so here goes.......


I am working with an excel document which lists repairs needed to be completed and arrival dates. What i am looking to do is if the date in cell A1 has been passed by 4 days, I want excel to change the row of this item to a different colour to signify that this service needs to be addressed.

Additionaly, however, and this is where im getting really stuck. I need excel to also send a notification email through outlook to all the technicians that this repair has passed the 4 day turnaround deadline.

Is this possible at all? Can i set this up so that new entries are also covered by this rule?

Thanks again all greatly appreciated!
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Hi WarrenMU and welcome to the forum.

You could color the row using the Conditional Formatting (CF) feature. Do a web search for something like Excel Conditional Formatting and you'll find several step-by-step guides.

The CF formula would look something like this...
Formula is =Today()-$A1>3


When (what event) do you want to check the dates in column A; when the workbook is opened; when you click a button, or run a macro?
 
Last edited:
Upvote 0
Hi Thanks for responding, I tried the today function but for some reason it seems to be defaulting to the date the document was created (oct 30th) and not the current date? The reason is when i change dates to the 8th, 3 days after today the "3 Days In" does not show but if I make it todays date the 5th it does?

this is the if statement i have used,

=IF(G40<(TODAY()+3),"3 DAYS IN",IF(G40<(TODAY()+7),"7 DAYS IN",""))

I would like the document to check every morning, is there a macro to do this, or just have it check upon opening of the document each day?

Thanks Much you help is greatly appreciated!
 
Upvote 0
The formula logic doesn't look right. Try this...
=IF(G40+7< TODAY(),"7 DAYS IN",IF(G40+3< TODAY(),"3 DAYS IN",""))<today(),"7 days="" in",if(g40+3<today(),"3="" in",""))



If you want to run the macro on open, create this macro in the ThisWorkbook code module
Code: 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Workbook_Open()
    [COLOR=darkblue]Call[/COLOR] MyMacro
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Where MyMacro is the name of your macro located in a standard code module (Module1) that checks the dates and emails.</today(),"7>
 
Upvote 0
Thanks again! I tried the code you suggested but that didn't work either, as i increment the dates the "text" never comes up. I started on the 5th and worked up to the 15th but no change, the other one changed, but held the "today" as the wrong date. Any other suggestions I can try?

AlphaFrog said:
The formula logic doesn't look right. Try this...
=IF(G40+7< TODAY(),"7 DAYS IN",IF(G40+3< TODAY(),"3 DAYS IN",""))<TODAY(),"7 in?,??))



If you want to run the macro on open, create this macro in the ThisWorkbook code module
Code: 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Workbook_Open()
    [COLOR=darkblue]Call[/COLOR] MyMacro
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Where MyMacro is the name of your macro located in a standard code module (Module1) that checks the dates and emails.</TODAY(),"7>
Click to expand...
 
Upvote 0
k think i got that one, this worked

=IF(G40= TODAY()+7,"7 DAYS IN",IF(G40= TODAY()+3,"3 DAYS IN",""))
 
Upvote 0
So one last question, i am reading the link you sent in regards to hooking the spreadsheet to have outlook send out an email. How do I specify that if upon the 3 day and the 7 day and email is to be sent out to service staff. I would have to send to about 5 individuals. Im still kinda new to the whole VBA coding, sorry if these are stupid questions, im just unsure..
 
Upvote 0
Code: 
[COLOR=darkblue]Sub[/COLOR] MyMacro()
    [COLOR=darkblue]Dim[/COLOR] cell [COLOR=darkblue]As[/COLOR] Range
    [COLOR=green]'Loop through each used cell in column H[/COLOR]
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] cell [COLOR=darkblue]In[/COLOR] Sheets("Sheet1").Range("H2", Range("H" & Sheets("Sheet1").Rows.Count).End(xlUp))
        [COLOR=darkblue]If[/COLOR] Left(cell.Value, 1) = "3" [COLOR=darkblue]Then[/COLOR] [COLOR=green]'Test if 3 days in[/COLOR]
            [COLOR=green]'3 day email code goes here[/COLOR]
            [COLOR=green]'Use cell.Offset(0, column) to reference another cell in the same row offset from column H[/COLOR]
        [COLOR=darkblue]ElseIf[/COLOR] Left(cell.Value, 1) = "7" [COLOR=darkblue]Then[/COLOR] [COLOR=green]'Test if 7 days in[/COLOR]
            [COLOR=green]'7 day email code goes here[/COLOR]
            [COLOR=green]'Use cell.Offset(0, column) to reference another cell in the same row offset from column H[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] cell
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Date reminder 7 days before
Replies
5
Views
600
awoohaw
awoohaw
E
  • Question
How do I create a Pop-up Notification/Reminder in Excel?
2 3 4
Replies
34
Views
2K
excelnewbie999
E
I
  • Question
How to chart when the test fail in number of test.
Replies
0
Views
151
i200yrs
I
J
  • Question
Conditional Formatting
Replies
2
Views
274
awoohaw
awoohaw
W
  • Solved
HELP! SUMIFS/Logic/Dates Functions
2
Replies
10
Views
868
Alex Blakenburg
A

Forum statistics

Threads
1,214,381
Messages
6,119,192
Members
448,874
Latest member
Lancelots

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