Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    formula - replacing blank with a date (2002 SP3)

    Hi,

    I need to identify specific claims that have not been determined (accepted or rejected) within 64 days. I need to calculate the difference between the date they were opened and how many days have elapsed up to the date they were determined or the end of the current month (for those with no date determined).

    My first step is to identify those claims with a blank determination date (column DR), and then replace it with the last day of the current month (eg 29/02/2008).
    I created a column INSERT_Date with the formula
    ^=IF(DR2=0,29/2/2008,DR2)
    however the blank entries are returning the date 0/01/1900 rather than 29/2/2008.
    I even tried
    ^=IF(DR2=" ",29/2/2008,DR2)
    but still get 0/01/1900

    Can someone tell me what I am doing wrong?

    Thanks
    capri

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula - replacing blank with a date (2002 SP

    Your "true" portion of your formula isn't the 29th of February 2008, it's 29/2/2008 or 0.00722111553784861. Since you have the cell formated as a date, your are being shown the date for the value 0.00722111553784861, which is actually 10 minutes and 24 seconds after midnight on 1/1/1900.

    Your true part of the statement should be something along the lines of:
    DATE(YEAR(NOW()),MONTH(NOW())+1,1)-1

    The full formula:
    =IF(DR2=0,DATE(YEAR(NOW()),MONTH(NOW())+1,1)-1,DR2)

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Thanks

    Thanks mbarron,

    Your formula works great. As a bonus I won't have to change it each month.
    Thanks for explaining about Excel viewing the date as a number. I frequently have to do calculations based on dates, and I should be able to adapt this formula for other uses as well.

    capri

Posting Permissions

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