+ Reply to Thread
Results 1 to 3 of 3

start date in H2 my renewal column is automatically given a date 12/01/1901

  1. #1
    Registered User
    Join Date
    08-19-2011
    Location
    copperas cove, tx
    MS-Off Ver
    Excel 2003
    Posts
    9

    Unhappy start date in H2 my renewal column is automatically given a date 12/01/1901

    I have a spread sheet

    A column is - Last Name

    G2 column is - DATE COMPLETED
    They input the date the completed the course

    H2 Column is - EXPIRES IN
    This is set to give me the date 2 years from DATE COMPLETED

    I2 Column is - Days until I need to renew

    J2 Column is - STATUS

    Problem : If I don't enter a start date in H2 my renewal column is automatically given a date 12/01/1901 as an expiration date, which in turn makes I2 a negative number -40043, and makes the STATUS past due

    What I would like do is if there is no date entered in date completed(G2) then Expires in (h2) is left blank along with DAYS REMAINING (I2) and STATUS(J2) could show idk NA. Its seems simple and I have tried hard, but I am at my end and in over my head. Any help would be appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: start date in H2 my renewal column is automatically given a date 12/01/1901

    Try

    =IF(ISBLANK(G2),"",EDATE(G2,24))

    in H2 and

    =IF(H2<>"",H2-TODAY(),NA())

    in I2. Then copy down in both columns.
    Martin

  3. #3
    Registered User
    Join Date
    08-19-2011
    Location
    copperas cove, tx
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: start date in H2 my renewal column is automatically given a date 12/01/1901

    Thank you so much!!! Worked great

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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