|
Date Reminders
Hi
I have a list of exam dates and I need to do refresher courses every so often. I would like excel to remind me 1 month before a course refresher is due and also if I am out of date for any courses. What is the best way to go about this in excel? Thanks |
Date Reminders
Use conditional formatting. Look it up in help, and the post back with rules
that you want to embed for further help. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Mariner" wrote in message ... Hi I have a list of exam dates and I need to do refresher courses every so often. I would like excel to remind me 1 month before a course refresher is due and also if I am out of date for any courses. What is the best way to go about this in excel? Thanks |
Date Reminders
Thanks for that. At present i use:
=DATEDIF(D4,$BB$3,"d")365 Then I use the true/false result with conditional formatting to give a green box if in date, a red box if out of date. Cell BB3 uses the =TODAY() function to get the current date. What I would also like is another value 1 month before I am out of date which I could then format yellow. "Bob Phillips" wrote: Use conditional formatting. Look it up in help, and the post back with rules that you want to embed for further help. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Mariner" wrote in message ... Hi I have a list of exam dates and I need to do refresher courses every so often. I would like excel to remind me 1 month before a course refresher is due and also if I am out of date for any courses. What is the best way to go about this in excel? Thanks |
Date Reminders
Mariner,
Firstly, I wouldn't store TODAY() in a cell, I would use =DATEDIF(D4,TODAY(),"d")365 With DATEDIF you cannot have a month early, but you could just do 30 days early, like =DATEDIF(D4,TODAY(),"d")335 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Mariner" wrote in message ... Thanks for that. At present i use: =DATEDIF(D4,$BB$3,"d")365 Then I use the true/false result with conditional formatting to give a green box if in date, a red box if out of date. Cell BB3 uses the =TODAY() function to get the current date. What I would also like is another value 1 month before I am out of date which I could then format yellow. "Bob Phillips" wrote: Use conditional formatting. Look it up in help, and the post back with rules that you want to embed for further help. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Mariner" wrote in message ... Hi I have a list of exam dates and I need to do refresher courses every so often. I would like excel to remind me 1 month before a course refresher is due and also if I am out of date for any courses. What is the best way to go about this in excel? Thanks |
Date Reminders
Hi Bob
Thats a simpler formula. As you maybe can tell I am new to excel. Instead of the formula returning true or false would it be possible for the formula to return for instance 1, 2 or 3. So if the result is less than 335 it returns 1. If less than 365 but more than 335 it returns 2. If more than 365 it returns 3. Thanks Mariner "Bob Phillips" wrote: Mariner, Firstly, I wouldn't store TODAY() in a cell, I would use =DATEDIF(D4,TODAY(),"d")365 With DATEDIF you cannot have a month early, but you could just do 30 days early, like =DATEDIF(D4,TODAY(),"d")335 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Mariner" wrote in message ... Thanks for that. At present i use: =DATEDIF(D4,$BB$3,"d")365 Then I use the true/false result with conditional formatting to give a green box if in date, a red box if out of date. Cell BB3 uses the =TODAY() function to get the current date. What I would also like is another value 1 month before I am out of date which I could then format yellow. "Bob Phillips" wrote: Use conditional formatting. Look it up in help, and the post back with rules that you want to embed for further help. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Mariner" wrote in message ... Hi I have a list of exam dates and I need to do refresher courses every so often. I would like excel to remind me 1 month before a course refresher is due and also if I am out of date for any courses. What is the best way to go about this in excel? Thanks |
Date Reminders
=IF(DATEDIF(D4,TODAY(),"d")365,3,IF(DATEDIF(D4,TO DAY(),"d")335,2,1))
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Mariner" wrote in message ... Hi Bob That's a simpler formula. As you maybe can tell I am new to excel. Instead of the formula returning true or false would it be possible for the formula to return for instance 1, 2 or 3. So if the result is less than 335 it returns 1. If less than 365 but more than 335 it returns 2. If more than 365 it returns 3. Thanks Mariner "Bob Phillips" wrote: Mariner, Firstly, I wouldn't store TODAY() in a cell, I would use =DATEDIF(D4,TODAY(),"d")365 With DATEDIF you cannot have a month early, but you could just do 30 days early, like =DATEDIF(D4,TODAY(),"d")335 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Mariner" wrote in message ... Thanks for that. At present i use: =DATEDIF(D4,$BB$3,"d")365 Then I use the true/false result with conditional formatting to give a green box if in date, a red box if out of date. Cell BB3 uses the =TODAY() function to get the current date. What I would also like is another value 1 month before I am out of date which I could then format yellow. "Bob Phillips" wrote: Use conditional formatting. Look it up in help, and the post back with rules that you want to embed for further help. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Mariner" wrote in message ... Hi I have a list of exam dates and I need to do refresher courses every so often. I would like excel to remind me 1 month before a course refresher is due and also if I am out of date for any courses. What is the best way to go about this in excel? Thanks |
Date Reminders
Thanks Bob
Works Great "Bob Phillips" wrote: =IF(DATEDIF(D4,TODAY(),"d")365,3,IF(DATEDIF(D4,TO DAY(),"d")335,2,1)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Mariner" wrote in message ... Hi Bob That's a simpler formula. As you maybe can tell I am new to excel. Instead of the formula returning true or false would it be possible for the formula to return for instance 1, 2 or 3. So if the result is less than 335 it returns 1. If less than 365 but more than 335 it returns 2. If more than 365 it returns 3. Thanks Mariner "Bob Phillips" wrote: Mariner, Firstly, I wouldn't store TODAY() in a cell, I would use =DATEDIF(D4,TODAY(),"d")365 With DATEDIF you cannot have a month early, but you could just do 30 days early, like =DATEDIF(D4,TODAY(),"d")335 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Mariner" wrote in message ... Thanks for that. At present i use: =DATEDIF(D4,$BB$3,"d")365 Then I use the true/false result with conditional formatting to give a green box if in date, a red box if out of date. Cell BB3 uses the =TODAY() function to get the current date. What I would also like is another value 1 month before I am out of date which I could then format yellow. "Bob Phillips" wrote: Use conditional formatting. Look it up in help, and the post back with rules that you want to embed for further help. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Mariner" wrote in message ... Hi I have a list of exam dates and I need to do refresher courses every so often. I would like excel to remind me 1 month before a course refresher is due and also if I am out of date for any courses. What is the best way to go about this in excel? Thanks |
| All times are GMT +1. The time now is 09:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com