1. ## Revised Formula (2002)

Hi Loungers, I have a formula (curtsey of a previous post) that I need to modify:-
=IF(G6=0,"",IF(L6<>"","Complete",IF(J6="","",IF(J6 >TODAY(),"Not Due",IF(NETWORKDAYS(J6,TODAY())>0,NETWORKDAYS(J6,T ODAY())&" working days overdue","")))))
I need to modify so that it will return a message "One Month Left" when the date value is one month from the date calculated at J6.
I've been trying to figure it out, but need some expert Loungers support

2. ## Re: Revised Formula (2002)

The following formula uses the EDATE function; you must have installed the Analysis Toolpak add-in that comes with Excel (Tools | Add-Ins...) to be able to use it.

=IF(G6=0,"",IF(L6<>"","Complete",IF(J6="","",IF(J6 >TODAY(),IF(J6=EDATE(TODAY(),1),"One Month Left","Not Due"),IF(NETWORKDAYS(J6,TODAY())>0,NETWORKDAYS(J6, TODAY())&" working days overdue","")))))

3. ## Re: Revised Formula (2002)

Hans - you've done it again!! Thankyou!

Is there away that the "One Month Left" calculation could trigger an email to be sent to selected persons?

4. ## Re: Revised Formula (2002)

Formulas don't trigger actions. You could write VBA code that checks the date and sends an e-mail if necessary. Very simple code to send an e-mail could look like this:

ActiveWorkbook.FollowHyperlink "mailto:billg@microsoft.com?Subject=Reminder&Body= Just one month left!"

For more sophisticated e-mails, you would have to automat Outlook, i.e. control Outlook from Excel using code.

5. ## Re: Revised Formula (2002)

Hans,

Thanks once again - you have save me hours and hours of work - If I can ever get the chance to buy you a beer/coffee etc I'd be most greatfull!!

Will give it try. Can VBA code be amended to include a persons name in the subject line, ie "Bill Smith has just one month left".

Also will it trigger the event if the spread sheet is closed or should it be left open and minimised (the file sits on a server)

6. ## Re: Revised Formula (2002)

1. The argument to Application.FollowHyperlink is a string; in my example it was a fixed string, but you can concatenate various parts, e.g.

Dim strPerson As String
strPerson = "Bill Smith"
strPerson & " has just one month left."

However, the body part is limited. For more control, you would have to use MAPI or automate Outlook.

2. You could run the code when the workbook is being opened, and you could use the Windows scheduler to open the workbook at regular intervals.

You can also use Application.OnTime to let a procedure call itself after a fixed time interval. For example:

Sub DoMail()
' code to test and send e-mails
...

' Run DoMail again after 1 day.
Application.OnTime Now() + 1, "DoMail"
End Sub

The workbook must remain open all the time for this to work. If the workbook is closed, nothing happens.

7. ## Re: Revised Formula (2002)

Hans,

The persons names are in column G - say G6 is Bill Smith. So when Bill Smith (or anyone in column G for that matter) as one month left, the message 'Bill Smith as one month left' would appear in the subject line.

As I understand your code in your previous post, Bill Smith would always appear in the subject line unless the code was changed, if my assumption is correct that would defeat the purpose of sending the email.

Any thoughts?

8. ## Re: Revised Formula (2002)

Hans,

There seems to be a small problem with the formula you posted. I've attached a small sample of the data and some explainations.

Would you mind having a look - I've tried adjusting the edate to -1 but that does not seem to work.

9. ## Re: Revised Formula (2002)

Checkout Ron DeBruin's site for some coding examples

Steve

10. ## Re: Revised Formula (2002)

Instead of setting strPerson = "Bill Smith" you could use strPerson = Range("G6") so that the message will contain whatever name is in cell G6.

The site Steve pointed you to contains much more information on how to send e-mails from Excel.

11. ## Re: Revised Formula (2002)

Sorry, I don't understand what you want. Checking if the due date is one month after today is the same as checking if today is one month before the due date, isn't it?

12. ## Re: Revised Formula (2002)

Hans, Sorry about the delay in getting back to you.

I'll try to clarify.

I want the formula to return a message "one month left" when the date is within one month of the due date in column F. That date is calculated from a date in column D = 90days. So the messages in column G would be "No Due" that message would stay until the date was less <=1 month prior to the date in column F and would read "One Month Left" and stay there until the date was >= the date in column F where it would calculate the number of days overdue as it does now.

I hope that helps

13. ## Re: Revised Formula (2002)

Aha, originally, you stated "when the date value is one month from the date calculated". I took this to mean "exactly one month".

If you change F4=EDATE(TODAY(),1) to F4<=EDATE(TODAY(),1) in the formula in G4 in your sample workbook, and fill down, it should check that "the date value is within one month from the date calculated".

14. ## Re: Revised Formula (2002)

Hans - thats it!!

You mentioned in one of your previous posts that a the VBA code is simple (maybe to you!) - I went to the site you suggested, however I can't find an example that will trigger an email based on a cell value - ie "One Month Left" and put the persons name and "Text" in the subject line.

Would you be able to help with a sample of what the code would be to generate emails based on the trigger - ie one for each person who has "One Month Left" in column G, also I'm concerned that the code may generate emails for as long as "One Month Left" in in the cell - can the code include a provision that only sends email either once or a predetermined freqencies?

15. ## Re: Revised Formula (2002)

See attached workbook. Click the button on the worksheet to create e-mails. When an e-mail has been created, the corresponding cell in column I isfilled with "Sent" to prevent creating an e-mail next time. If you want to reset things, just clear column I.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•