1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Due date alarm

Discussion in 'Ask an Excel Question' started by Thomas Kuriakose, Jan 12, 2018.

  1. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    655
    Respected Sirs,

    We have an Invoice tracker, where we need to have a check whether the Invoice date (column C) has crossed 130 days from the today and give alert or alarm in the same tab or in another tab and it should be in red color.

    If the Invoice has been paid (column F) then the status should be paid and in green color.

    Kindly suggest the best ways to get the alert/alarm and color.

    Thank you very much,

    with regards,
    thomas

    Attached Files:

  2. S. Das

    S. Das Member

    Messages:
    60
    Sir, I have one doubt in that line
    The status (column E) will be given by whom user or the system?
    I made something in which user has to give the status and system will just make column f green colored.
    Please check the attached file, and tell me if it is helpful

    Attached Files:

    Syndp and Thomas Kuriakose like this.
  3. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    655
    Respected Sir,

    Thank you for your support on this query.

    The column (E) I manually input to check the number of payments as there are 15k rows of data. The paid conditional format is good.

    But, we need the alert/alarm for the due invoices greater than 130 days in red color.

    Does the alarm/alert require a vba or a formula solution will do if we need to move this to the Alert tab.

    Thank you very much,

    with regards,
    thomas
  4. S. Das

    S. Das Member

    Messages:
    60
    please check this...

    The attached file Due Date Alarm.xlsx is based on the formula and Due Date Alarm.xlsm is based on VBA.

    The 1st attachment has a problem that is if condition false then it should leave a blank row, which is not in the 2nd one

    Regards,
    Sandip

    Attached Files:

    Last edited: Jan 12, 2018
  5. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    655
    Respected Sir,

    Thank you very much for these two solutions.

    Much appreciated,

    with regards,
    thomas
  6. Syndp

    Syndp New Member

    Messages:
    4
    Thank you @S. Das for the notations in VBA, which will help me in future.
    S. Das likes this.
  7. S. Das

    S. Das Member

    Messages:
    60
  8. GraH - Guido

    GraH - Guido Active Member

    Messages:
    117
    Small improvement in the solution with conditional formatting.
    Question: if line is red, this is the alert, why you need an "extra" alert? Seems a bit redundant.
    Try to add new data below the table. CF should also auto-extend.

    Attached Files:

    Thomas Kuriakose likes this.
  9. S. Das

    S. Das Member

    Messages:
    60
    @GraH - Guido, your solution is okey and first I think that solution but there is one problem. If user has large data (contains 3000 rows) then user have to check all the rows for alerts and have to scroll carefully for it to avoid any mistake. I personally faced this type of mistakes earlier.

    But there is no chance of this type of error in the solution using VBA.
    Last edited: Jan 13, 2018
    Syndp likes this.
  10. GraH - Guido

    GraH - Guido Active Member

    Messages:
    117
    Fair, but what about filter (on colour) to generate a distinct list?
    Syndp and S. Das like this.
  11. S. Das

    S. Das Member

    Messages:
    60
    Sorry, my mistake, I forgot this point and you are very smart.
  12. GraH - Guido

    GraH - Guido Active Member

    Messages:
    117
    You are too kind and no need for a sorry... Also, one might inverse the logic. Why bother with CF and filter if VBA does all... A solution should be lean, simple and obviously working. You were clever to offer both possibilities.
    S. Das likes this.
  13. Syndp

    Syndp New Member

    Messages:
    4

Share This Page