formula – replacing blank with a date (2002 SP3)

Topic

New Reply

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

Reply | Quote

Replies

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)

Reply | Quote

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

Reply | Quote