Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel: Reminder/Pop-up message
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel: Reminder/Pop-up message
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(TO DAY())+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. -- Please remember to indicate when the post is answered so others can benefit from it later. "Razak" wrote: 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel: Reminder/Pop-up message
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" wrote: 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(TO DAY())+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. -- Please remember to indicate when the post is answered so others can benefit from it later. "Razak" wrote: 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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel: Reminder/Pop-up message
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). -- Please remember to indicate when the post is answered so others can benefit from it later. "Razak" wrote: 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" wrote: 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(TO DAY())+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. -- Please remember to indicate when the post is answered so others can benefit from it later. "Razak" wrote: 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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel: Reminder/Pop-up message
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 "KC Rippstein" wrote: 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). -- Please remember to indicate when the post is answered so others can benefit from it later. "Razak" wrote: 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" wrote: 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(TO DAY())+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. -- Please remember to indicate when the post is answered so others can benefit from it later. "Razak" wrote: 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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel: Reminder/Pop-up message
Hi KC,
Just to add, what changes to I have to make.. to make it as weekly basis instead of months? Thanks "KC Rippstein" wrote: 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). -- Please remember to indicate when the post is answered so others can benefit from it later. "Razak" wrote: 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" wrote: 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(TO DAY())+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. -- Please remember to indicate when the post is answered so others can benefit from it later. "Razak" wrote: 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 |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel: Reminder/Pop-up message
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(TOD AY()),1),H2:H51,1))*(F5:F100<=VLOOKUP(DATE(H1,MONT H(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 -- Please remember to indicate when the post is answered so others can benefit from it later. "Razak" wrote: Hi KC, Just to add, what changes to I have to make.. to make it as weekly basis instead of months? Thanks "KC Rippstein" wrote: 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). -- Please remember to indicate when the post is answered so others can benefit from it later. "Razak" wrote: 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" wrote: 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(TO DAY())+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. -- Please remember to indicate when the post is answered so others can benefit from it later. "Razak" wrote: 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 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel: Reminder/Pop-up message
Sorry, that SUMPRODUCT formula should be:
=IF(SUMPRODUCT((F5:F100=VLOOKUP(DATE(H1,MONTH(TOD AY()),1),H2:H51,1))*(F5:F100<NextWeek)),"Term's this week!!","") -- Please remember to indicate when the post is answered so others can benefit from it later. "KC Rippstein" wrote: 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(TOD AY()),1),H2:H51,1))*(F5:F100<=VLOOKUP(DATE(H1,MONT H(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 -- Please remember to indicate when the post is answered so others can benefit from it later. "Razak" wrote: Hi KC, Just to add, what changes to I have to make.. to make it as weekly basis instead of months? Thanks "KC Rippstein" wrote: 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). -- Please remember to indicate when the post is answered so others can benefit from it later. "Razak" wrote: 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" wrote: 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(TO DAY())+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. -- Please remember to indicate when the post is answered so others can benefit from it later. "Razak" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
is there any way to have a type of reminder set up from excel | Setting up and Configuration of Excel | |||
Reminder message before printing | Excel Discussion (Misc queries) | |||
Reminder in Excel? | Excel Worksheet Functions | |||
Reminder in excel | Excel Discussion (Misc queries) | |||
can i have a pop up reminder message when i open a workbook? | New Users to Excel |