Excel: Reminder/Pop-up message

[Razak]

Hi Guru out there,

I would just like to ask for all of your help. I actually know basic from
Excel - I'm using version 2002.

I have an excel sheet, that has a column of " Termination Date " its date
format was 20081112(yyyy/mm/dd). Can you help me, on how to put a reminder
or pop-up message that I have termination date on or before 20081130 (Nov.30,
2008)?

Also, if possible to turn to color RED - once it detected that its date are
for future dates? let say, I have termination date for Nov.30 and Dec. 10..
all future dates are automatically turn to color RED?

Hope that I explain it properly.

Thanks so much for your help

 

[KC Rippstein hotmail com>]

Perhaps you can do this.
For the reminder, just set a designated area at the top, say in F2, where
you type the following formula (I assumed term date is column F and data
starts in row 5 and ends in row 100):
=IF(SUMPRODUCT((F5:F100>=DATE(YEAR(TODAY()),MONTH(TODAY()),1))*(F5:F100<=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))),"Term's this month!!","")
You can then format the font for that cell to really stand out to you and
even apply conditional formatting to shade the background if the cell value
is "Term's this month!!"

For the highlighting in your term column to show you future dates, you'll
want to use conditional formatting. First we are going to go back to the top
of our worksheet and go off the print area, say J1, and just type this
formula:
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1), then we're going to name that cell
(using the white name box where it says you're in cell J1) and we're gonna
call it NextMonth. Now just highlight the term column data (F5:F100, for
example) and go to Format > Conditional Formatting. For condition 1, change
the drop down from "Cell Value Is" to "Formula Is" and type this in:
=F5>=NextMonth
then change the pattern, font, and/or border as you like.

 

[Razak]

Hi KC,

thanks much for time looking at my query.

anyways, do i have to change the format of the said date, from 20081113 to
2008-11-13? because I apply your created code or programme all the date turn
all into RED.

is there a way that it will create a message (pop-up message) that I have
the following Termination future dates? (like it will message: " You Have
Term Date Today " referring to todays date..)

Thanks = )

 

[KC Rippstein hotmail com>]

It sounds like your "dates" are actually text. You should convert them to
dates. Just click on a blank cell way off to the right and copy it, then go
to your date column, select all your dates, and click Edit > Paste Special >
Add. This should add "nothing" to your dates and, as a result, convert them
from text to dates.

Pop-up messages are only possible with a macro (to my knowledge).

 

[Razak]

Hi KC,

I've follow the steps below. But nothing happen.. still same. Also, for
the pop-up message, how can use that macro?

Thanks

 

[Razak]

Hi KC,

Just to add, what changes to I have to make.. to make it as weekly basis
instead of months?

Thanks

 

[KC Rippstein hotmail com>]

Weekly tracking is much more difficult. As you could tell from the formulas
below, years, months, and days are common and simple to work with.

To start, let's enter the year in cell H1 (or somewhere to the right off the
print area). You can even change the font & color of that cell so you know it
is an input field.

H2 will begin listing the dates which start each week and go down 52 rows to
the
end of the year. I have used Monday as the start date for each week.

Okay, so in H2 we'll have the first Monday for the week containing 1/1 of
the year shown in H1. H2 needs this formula (sorry, I don't understand the
formula, it came from MVP Ron de Bruin in the Netherlands, so I don't know
how to change it from Mondays to another day):
=IF(ISERROR(DATE(1900,1,1)),IF(H1=1904,DATE(1904,1,4),DATE(H1,1,0)-MOD(DATE(H1,1,0)-2,7)+(7*(MOD(DATE(H1,1,0)-2,7)>3))+1),IF(H1=1900,DATE(1900,1,1),DATE(H1,1,1)-MOD(DATE(H1,1,1)-2,7)+(7*(MOD(DATE(H1,1,1)-2,7)>3))))

Then select H3:H51 and type in this formula:
=H2+7
and commit using Ctrl+Enter.

Now H2:H51 should show the Monday which starts a new week in chronological
order for 52 weeks.

So the SUMPRODUCT formula in F2 should now change to the following:
=IF(SUMPRODUCT((F5:F100>=VLOOKUP(DATE(H1,MONTH(TODAY()),1),H2:H51,1))*(F5:F100<=VLOOKUP(DATE(H1,MONTH(TODAY())+1,0),H2:H51,1))),"Term's this week!!","")

Now we need to change J1 (which we had called NextMonth). Go to Edit >
Insert Names > Define, select NextMonth from the list, and delete it. Close
out of that and go back to J1 to type in this new formula:
=VLOOKUP(DATE(H1,MONTH(TODAY()),DAY(TODAY()+7)),H2:H51,1)
We'll name this NextWeek.

To change the conditional formatting, highlight your term dates (F5:F100,
for example) and go to Format > Conditional Formatting. We'll change the
formula we had used to this:
=TEXT(F5,"yyyymmdd")>=TEXT(NextWeek,"yyyymmdd")

As for a macro to issue a pop-up message, that's fairly simple. Right-click
the sheet tab and select "View Code" and the VBA editor will pop up. Just
type this in:
Sub Worksheet_Activate()
If Cells(2,"F").Value = "Term's this month!!" Then _
MsgBox("Term's this week!!")
End If
End Sub

 

[KC Rippstein hotmail com>]

Sorry, that SUMPRODUCT formula should be:
=IF(SUMPRODUCT((F5:F100>=VLOOKUP(DATE(H1,MONTH(TODAY()),1),H2:H51,1))*(F5:F100<NextWeek)),"Term's this week!!","")

 

[chinjoo]

Hi KC,
It is possible to post the templete worksheet that you illustrated above?

Chinjoo