+ Reply to Thread
Results 1 to 14 of 14

How to: Formula to check overdue date and mark in RED the name of company.

  1. #1
    Registered User
    Join Date
    12-05-2016
    Location
    Bulgaria
    MS-Off Ver
    2010
    Posts
    26

    How to: Formula to check overdue date and mark in RED the name of company.

    Dear Excel Experts,
    I have an issue. I am trying to make a formula which must mark in red the name of the company in RED if it is with overdue invoices. I am sending you simple table.
    The formula must check the date (today) and after that search for the name of company and after that to check the payment delay and if it is overdue it must mark in red the name of the company. I think that this is the way, but I am not a expert and may be is not the right way.
    I will appreciate your help!
    Thank is advance.
    EXCEL.jpg

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: How to: Formula to check overdue date and mark in RED the name of company.

    how do you know if the invoice is overdue or has been paid
    OR are these always a list of unpaid invoices

    so the due by date would be

    C2+E2

    that would give the date its due to be paid

    so if today() is greater than that date its overdue

    =Today() > (C2+E2)

    which is your conditional formatting rule for column A

    would have been easier if you uploaded a sample sheet , saves me having to make one with your data

    probably need to add a condition to stop blank cells being coloured

    for 2007, 2010 or 2013 excel version
    Conditional Formatting

    Highlight applicable range >>
    A2:A1000

    Home Tab >> Styles >> Conditional Formatting
    New Rule >> Use a formula to determine which cells to format
    Edit the Rule Description: Format values where this formula is true:

    =AND(C2<>"", Today() > (C2+E2))

    Format… [Number, Font, Border, Fill]
    choose the format you would like to apply when the condition is true
    OK >> OK
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: How to: Formula to check overdue date and mark in RED the name of company.

    You'll need to sort the format of your date in column C to 10/09/2016 or similar to ensure it reads as date for the formula but use the attached pic as the guide to use conditional formatting

  4. #4
    Registered User
    Join Date
    12-05-2016
    Location
    Bulgaria
    MS-Off Ver
    2010
    Posts
    26

    Re: How to: Formula to check overdue date and mark in RED the name of company.

    Thank you for your fast reply!
    In the attached file you can see a simple excel sheet.
    Thank you in advance!
    Best regards,
    http://www.filedropper.com/acc_3

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: How to: Formula to check overdue date and mark in RED the name of company.

    The dates are text and not real dates
    so i have changed them to real dates and used your format with dots
    so now the formula i posted will work as shown

    in H i have shown the due date - C3 + E3
    and then in I , i have just shown if that date is less than today , and so should be flagged

    see attached modified file

    how do you get the dates into the sheet

    i can show you a quick way to change the format using
    Datevalue( left, mod , right formulas)
    will convert to a valid date
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    12-05-2016
    Location
    Bulgaria
    MS-Off Ver
    2010
    Posts
    26

    Re: How to: Formula to check overdue date and mark in RED the name of company.

    I am trying to make it, but there is something wrong and I can not do it.
    1. I make the due date
    2. I make the colums to check TRUE/FALSE
    3. When I type the formula in conditional formating it not happening nothing?
    4. I change the colums C to date(select all rows -> formating -> date), but again nothing..
    Thank you for the information, but I also want to make it myself to know what I actualy do.
    The situation is: I recieve the sheet like I sent you in the attached file and after that I must mark in red the companies which have overdue invoices. I make it line by line and now I want to make it faster with formulas.
    Thank you in advance.

  7. #7
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: How to: Formula to check overdue date and mark in RED the name of company.

    ok, your issue will be the date
    4. I change the colums C to date(select all rows -> formating -> date), but again nothing..
    the date is text entered as
    05.12.2016
    Thats NOT a date and no formatting will change that to a valid excel date
    And excel dates are just numbers counting from 1/1/1900

    so if you enter a valid date into a cell
    say today
    5/12/16

    that will show as a date
    then reformat the cell and change to general or number and you will see it changes to a number
    42709 in fact

    now thats a valid date and you can minipulate that as a date

    05.12.16
    is just text like ABC and when you try to apply date formula it will not work.

    SO what does that mean

    well that means as you get the spreadsheet sent each day you will need to change the dates into real dates
    to do that

    in column g add this formula
    =DATEVALUE(LEFT(C3,2)&"/"&MID(C3,4,2)&"/"&RIGHT(C3,4))

    then in conditional formatting you can use that column to highlight the invoices as red

    then all you should need to do each day is copy the range
    A3:E ???

    into this "template" and it should work for you each day

    and so in conditional formatting select all of column C
    now use this formula
    =AND(C1<>"", today()<$G1+$E1)
    which is using G to get the "real" date

    i have uploaded and file
    ......template_blank
    where you can copy cells A3:E200 from your daily sheet into the template

    now the file
    .....template
    shows the ACC data copied in and the result

    does that help?
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    12-05-2016
    Location
    Bulgaria
    MS-Off Ver
    2010
    Posts
    26

    Re: How to: Formula to check overdue date and mark in RED the name of company.

    Thank you etaf,
    I still have some troubles, but tomorrow I will try again.
    After that I will post again.
    Thank you again!!!

  9. #9
    Registered User
    Join Date
    12-05-2016
    Location
    Bulgaria
    MS-Off Ver
    2010
    Posts
    26

    Re: How to: Formula to check overdue date and mark in RED the name of company.

    Hello etaf,
    I do it!! Everything is working. Thank you so much! I appreciate your help.
    P.S Is it very difficult(for me may be is..) to send automatic email when some invoice is overdue and it is in RED? IF you have time, please tell me a little bit more about this functions.
    Best regards,

  10. #10
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: How to: Formula to check overdue date and mark in RED the name of company.

    you are welcome

    For the automatic email , i would suggest you create a new thread in the VBA forum
    There are quite a few threads on this subject

    VBA is not an area i reply to in forums,

  11. #11
    Registered User
    Join Date
    12-05-2016
    Location
    Bulgaria
    MS-Off Ver
    2010
    Posts
    26

    Re: How to: Formula to check overdue date and mark in RED the name of company.

    Okay thank you!
    Have a nice day, I wish best!

  12. #12
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: How to: Formula to check overdue date and mark in RED the name of company.

    thanks
    if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here

  13. #13
    Registered User
    Join Date
    12-05-2016
    Location
    Bulgaria
    MS-Off Ver
    2010
    Posts
    26

    Re: How to: Formula to check overdue date and mark in RED the name of company.

    Hello,
    What is wrong with the conditional formatting of this workbook? https://ufile.io/c64eb1
    Thank you in advance!
    P.S I do not know how to upload files directly here as attachment.

  14. #14
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: How to: Formula to check overdue date and mark in RED the name of company.

    Quote Originally Posted by ton4ito View Post
    Hello,
    What is wrong with the conditional formatting of this workbook? https://ufile.io/c64eb1
    Thank you in advance!
    P.S I do not know how to upload files directly here as attachment.
    Click Go Advance below the quick reply box beneath this comment.

    Then you can manage attachments and add that way.

    However as this thread has been marked Solve please start a new thread with an attachment.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. formula to show if something is up to date, due or overdue.. help!!
    By tahlialouisa in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-17-2015, 07:03 AM
  2. Automate Macro script needed to Mark Overdue ,SLA-HOLD,Soon to be Overdue by Hours
    By britishidol200 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-04-2015, 12:55 PM
  3. Replies: 2
    Last Post: 10-28-2013, 03:52 PM
  4. Formula to determine total overdue values by length of overdue
    By Midnight_Dragon in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-17-2013, 02:03 AM
  5. [SOLVED] Would like to trigger a check mark in one column when date is entered in another
    By Silverfoot in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 12-04-2012, 03:55 PM
  6. Duplicate Company Check by date
    By niladri20005 in forum Excel General
    Replies: 0
    Last Post: 08-22-2011, 02:07 PM
  7. Formula to mark record as overdue or current
    By sooshi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2008, 07:03 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1