+ Reply to Thread
Results 1 to 6 of 6

Comparing and Bucketing Dates

  1. #1
    Registered User
    Join Date
    10-27-2015
    Location
    Utah
    MS-Off Ver
    2013
    Posts
    3

    Angry Comparing and Bucketing Dates

    I am looking for help, I have two dates that I need to put in a 3rd collumn depending on 3 scenarios.

    F2 = Due Date (This will always have a date)
    H2 = Published Date (This will be null until published then have the publish date)
    I2 = On Time (This is where I am calculateing this needs to equal Not Due, Ontime, Past Due)

    I have not been able to work through the appropirate if statements to make this work, I try to compare against today and cant get anything to work from that point. Any thoughts from the community?

  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,732

    Re: Comparing and Bucketing Dates

    what are the rules in the IF statement for
    Not Due,
    Ontime,
    Past Due

    are you just comparing the date in Due to the Published date

    =IF( AND(today() <= F2, H2="") , "Past Due", IF( AND ( F2>=H2, H2<>"" ) , "on time", IF( AND(today() < F2, H2="") , "Not Due" , "??" )))
    Last edited by etaf; 10-27-2015 at 03:14 PM.
    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
    Registered User
    Join Date
    10-27-2015
    Location
    Utah
    MS-Off Ver
    2013
    Posts
    3

    Re: Comparing and Bucketing Dates

    I am comparing the due date to the publish date, I am also comparing a blank publish date to the due date and cross comparing it to today. Here is what I have so far. I may have it solved, I am validating the data to make sure I did it correct.

    =IF(AND(ISBLANK([@[Published Date]]), [@[Due Date]]>=TODAY()), "Not Due", IF(AND(ISBLANK([@[Published Date]]), [@[Due Date]]<=TODAY()), "Past Due", IF([@[Published Date]]<=[@[Due Date]], "On Time", IF([@[Published Date]]>=[@[Due Date]], "Past Due", ))))

  4. #4
    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,732

    Re: Comparing and Bucketing Dates

    OK - let us know if you still need help

  5. #5
    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,152

    Re: Comparing and Bucketing Dates

    =IF(H2<>"",IF(H2>F2,"Past Due","On Time"),"Not Due")
    Last edited by JohnTopley; 10-27-2015 at 03:37 PM.

  6. #6
    Registered User
    Join Date
    10-27-2015
    Location
    Utah
    MS-Off Ver
    2013
    Posts
    3

    Re: Comparing and Bucketing Dates

    It looks like John's solution works as well as the one I suggested earlier. Thanks for everyone's help getting this put together.

+ 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] Formating custom dates and comparing dates with different formats
    By OmniBlue in forum Excel General
    Replies: 2
    Last Post: 01-09-2014, 10:08 AM
  2. Comparing Dates
    By GeorgeE in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-30-2013, 12:39 PM
  3. [SOLVED] Bucketing cells with dates based on predefined ranges
    By TRS in forum Excel General
    Replies: 4
    Last Post: 08-16-2012, 09:36 PM
  4. [SOLVED] Comparing dates
    By y_not in forum Excel General
    Replies: 1
    Last Post: 06-29-2012, 06:30 AM
  5. Comparing 3 Different Dates
    By azwinelover in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-17-2011, 11:33 AM
  6. Bucketing Values
    By Dexta_Dark in forum Excel General
    Replies: 3
    Last Post: 05-20-2010, 01:29 PM
  7. Need help comparing dates
    By aben8057 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-31-2006, 05:16 PM
  8. Bucketing data based on DATE Range criteria
    By sumitk in forum Excel General
    Replies: 1
    Last Post: 05-19-2006, 07:16 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