+ Reply to Thread
Results 1 to 7 of 7

Can I make a cell display something if any date in a row is before today?

  1. #1
    Registered User
    Join Date
    04-10-2014
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    20

    Post Can I make a cell display something if any date in a row is before today?

    I currently have this formula in a cell which displays "Overdue" if the date in one particular cell (U7) is prior to today:

    =IF(ISBLANK(U7),"",IF(U7<TODAY(),"Overdue",""))

    This is useful but each row has 20 items and making 1 cell for each would take up a lot of space. I want the cell to display "Overdue" if any one date in the row has passed. Is there any way to do that?

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Can I make a cell display something if any date in a row is before today?

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    04-10-2014
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Can I make a cell display something if any date in a row is before today?

    Brilliant, thank you

  4. #4
    Registered User
    Join Date
    04-10-2014
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Can I make a cell display something if any date in a row is before today?

    Oh wait, just noticed in a few rows it throws a wobbly if they have a currency amount entered.

    For example, in one column I have amounts paid. If I have a number in here, it shows "Overdue" even if there is no overdue date. Do you know why the number throws it off and is there any way around this?

  5. #5
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Can I make a cell display something if any date in a row is before today?

    If it's always the same column which contains the currency - let's say for example, Column P, then use:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-10-2014
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Can I make a cell display something if any date in a row is before today?

    That's very helpful, thank you. One more question if I may - is there a way to make "Overdue" return if the date is "equal to or before" today's date, or is there only "<" for "before" today's date query?

  7. #7
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Can I make a cell display something if any date in a row is before today?

    Just use "<=" instead of "<" if today's date also triggers overdue

  8. #8
    Registered User
    Join Date
    04-10-2014
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Can I make a cell display something if any date in a row is before today?

    Brill, thank you very much!

+ 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. condit. format: If date in cell = today, display "Today"
    By ratkins in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-14-2013, 01:33 PM
  2. Display a Value based on Today's Date in Date Range
    By barella in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-12-2010, 06:25 AM
  3. Make TODAY() stay the same date
    By danison in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 01-02-2010, 07:47 PM
  4. Replies: 3
    Last Post: 02-26-2009, 04:28 PM
  5. If date in cell is today's date make it bold?
    By Sepia in forum Excel General
    Replies: 3
    Last Post: 11-14-2008, 01:24 PM

Tags for this Thread

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