Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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"
    ActiveWorkbook.FollowHyperlink "mailto:me@isp.com?Subject=Reminder&Body=" & _
    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. #7
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Revised Formula (2002)

    Checkout Ron DeBruin's site for some coding examples

    Steve

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #12
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #14
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?

    Thanks for your help

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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 LastLast

Posting Permissions

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