+ Reply to Thread
Results 1 to 15 of 15

Using Date in Cell to Generate Status with Overdue, or 30/60 Day Warning

  1. #1
    Registered User
    Join Date
    07-10-2015
    Location
    Buffalo, NY
    MS-Off Ver
    2013
    Posts
    5

    Using Date in Cell to Generate Status with Overdue, or 30/60 Day Warning

    I am working with a spreadsheet that already had the following formula in it to track OVERDUE, 30 Days and 60 Days away.

    Cell $V$1 has the formula =TODAY() to report the current date for comparison.
    Cell K2 tracks the date entered in the cell to start annual tracking.

    Formala:
    =IF(K2<$V$1,"OVERDUE", IF(K2<$V$1+30,"30 Days", IF(K2<$V$1+60,"60 Days", IF(K2>$V$1+61,"-"))))

    The issue I am running into is that K2 may be blank if tracking hasn't been started yet for that item.
    I cannot figure out how to modify the formula that if the cell (K2) is blank, the formula would return the word "Inventory" in the cell.

    I would greatly appreciate any help with this. I also recognize this may not be the best formula for this so other options would be welcome.

  2. #2
    Valued Forum Contributor
    Join Date
    04-26-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    502

    Re: Using Date in Cell to Generate Status with Overdue, or 30/60 Day Warning

    This should work for you.


    =IF(K2=0,"Inventory",IF(K2<$V$1,"OVERDUE", IF(K2<$V$1+30,"30 Days", IF(K2<$V$1+60,"60 Days", IF(K2>$V$1+61,"-")))))
    Last edited by BlindAlley; 07-10-2015 at 06:28 PM.

  3. #3
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,372

    Re: Using Date in Cell to Generate Status with Overdue, or 30/60 Day Warning

    deleted post

  4. #4
    Registered User
    Join Date
    07-10-2015
    Location
    Buffalo, NY
    MS-Off Ver
    2013
    Posts
    5

    Re: Using Date in Cell to Generate Status with Overdue, or 30/60 Day Warning

    That worked!

    With both versions of the formula, I run into a few scenarios where the current date is within a day or two before or after the =today() output where I receive back a false as the output. For example, today is 7/11/15, I receive back false for the dates of 9/09/15 and 9/10/15. I tested a few other dates in the system and the two days prior to the actual date will show up as False each time. I am concerned with this as someone else will be managing the data and sending email notifications regarding filtering on this and may miss the proper notification as timing is critical.

    If there something else I can do to modify the formula to prevent that error?

    I am now using the following formula:
    =IF(K2=0,"Inventory",IF(K2<$V$1,"OVERDUE", IF(K2<$V$1+30,"30 Days", IF(K2<$V$1+60,"60 Days", IF(K2>$V$1+61,"-")))))

    Thank you.

  5. #5
    Valued Forum Contributor
    Join Date
    04-26-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    502

    Re: Using Date in Cell to Generate Status with Overdue, or 30/60 Day Warning

    Sara,

    Could you post a sample of your workbook, (remove sensitive data 1st), it's hard to work on your last 'False' problem without seeing what else is happening.

    Even one row copied into a new sheet would work

    Steve

  6. #6
    Valued Forum Contributor
    Join Date
    04-26-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    502

    Re: Using Date in Cell to Generate Status with Overdue, or 30/60 Day Warning

    Sara,

    Try this first, it eliminates, the 'FALSE" errors.

    =IF(K5<=10,"INVENTORY",IF(K5<=29,"OVERDUE",IF(K5<=59,"30 Days",IF(K5<=89,"60 Days",IF(K5>=90,"PAST DUE")))))
    Where 10 is the count of Inventory days
    Where 29 is the Overdue period.
    Where 90 is panic :-)
    Attached Files Attached Files
    Last edited by BlindAlley; 07-11-2015 at 11:52 AM. Reason: add example file

  7. #7
    Registered User
    Join Date
    07-10-2015
    Location
    Buffalo, NY
    MS-Off Ver
    2013
    Posts
    5

    Re: Using Date in Cell to Generate Status with Overdue, or 30/60 Day Warning

    That last formula showed dates that were 30 days out as OVERDUE.

    I have attached a sample portion of data with a few of the conditions that would occur. I think this might help.

    Thanks again for the help.
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,359

    Re: Using Date in Cell to Generate Status with Overdue, or 30/60 Day Warning

    An alternative using VLOOKUP
    Attached Files Attached Files

  9. #9
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Using Date in Cell to Generate Status with Overdue, or 30/60 Day Warning

    What does "Days not False" and "Showing active count with -" means here??
    Happy to Help

    How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html

    "I don't get things easily, so please be precise and elaborate"

    If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
    If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.

    Sourabh

  10. #10
    Registered User
    Join Date
    07-10-2015
    Location
    Buffalo, NY
    MS-Off Ver
    2013
    Posts
    5

    Re: Using Date in Cell to Generate Status with Overdue, or 30/60 Day Warning

    For Clarification:

    The Output is Yielding False, the Expected Output should be reflecting 60 Days not False.
    The Output in the Cell is - , this means that the cell is active and dates are entered in the system but not yet at a 60 or 30 Day alarm.

  11. #11
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: Using Date in Cell to Generate Status with Overdue, or 30/60 Day Warning

    Hi

    Please see the files?

    One for Array formula work all excel 2003-2013 and other is AGGREGATE work from 2010 onward not before.

    I give you idea with this sample?

    Array
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Press same time CTRL+SHIFT+ENTER not just ENTER till you see like this {} then copy down
    or you on excel 2013

    Non Array
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Copy down
    Attached Files Attached Files
    Last edited by micope21; 07-11-2015 at 01:31 PM.
    To help you by my post? it would be nice to click on to say "Thank you".
    If you are happy with a solution to your problem?
    Click Thread Tools above your first post,
    select "Mark your thread as Solved".

  12. #12
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Using Date in Cell to Generate Status with Overdue, or 30/60 Day Warning

    Maybe this---
    Attached Files Attached Files

  13. #13
    Valued Forum Contributor
    Join Date
    04-26-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    502

    Re: Using Date in Cell to Generate Status with Overdue, or 30/60 Day Warning

    Sara,

    Fixed by using: =IF(C13=0,"Inventory",IF(C13<$G$2,"OVERDUE", IF(C13<$G$2+30,"30 DAYS", IF(C13<$G$2+60,"60 DAYS", IF(C13>=$G$2+61," - ")))))



    Steve
    Attached Files Attached Files
    Last edited by BlindAlley; 07-11-2015 at 02:05 PM.

  14. #14
    Registered User
    Join Date
    07-10-2015
    Location
    Buffalo, NY
    MS-Off Ver
    2013
    Posts
    5

    Re: Using Date in Cell to Generate Status with Overdue, or 30/60 Day Warning

    Thanks for your help Steve. That fixed it.

  15. #15
    Valued Forum Contributor
    Join Date
    04-26-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    502

    Re: Using Date in Cell to Generate Status with Overdue, or 30/60 Day Warning

    Glad to have helped Sara.

+ 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. [SOLVED] How to generate a status indicator
    By MissJemJem in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-29-2014, 09:18 AM
  2. How to Generate sheets on a Status of Equipment
    By ronettes in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-30-2014, 05:37 AM
  3. Replies: 0
    Last Post: 04-10-2012, 11:56 AM
  4. Replies: 1
    Last Post: 02-02-2012, 04:06 AM
  5. send email via excel for action items with overdue status
    By xiaotianshi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-21-2007, 11:21 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