Conditional Formula with Multiple Arguments

[p_johnsen]

I am running Excel 2003. I have a need to calculate the average number of days that my company's unpaid invoices are aging, and consider the actual days it took to receive payment. The objective is to calculate a single number - the average DSO (day sales outstanding) including both unpaid invoices and paid invoices. In a spreadsheet, I have Bid ID Numbers arrayed in Column A, Invoice Dates in Column B and Payment Received dates in Column C. Not all Bid ID Numbers will have a corresponding Invoice Date, because my company does not always win the bid. As a result, some rows have no Invoice Date in Column B (we lost the bid) and Payment Received date in Column C. Some rows have an Invoice Date in Column B (we won the bid) but no Payment Received date in Column C (we are waiting for payment). Some rows have dates in columns B and C (we have invoiced the client and received payment). I need to avoid the #VALUE! or #REF! error messages in the cells. I need help in creating a nested conditional formula that returns the number of days in Column D. If there is no date in Column B, then no value is returned in Column D. If there is an Invoice Date in Column B and no Payment Date in Column C, then the difference in days between Today() and the Invoice Date should show in Column D. If there is an Invoice Date in Column B and a Payment Date in Column C, then I need to return a value in Column D that represents the difference between the Invoice Date and the Payment Date. The single nested conditional formula in Column D needs to consider these three conditions outlined above. How do I do that??

 

[Marcelo Branco]

Maybe

D2
=IF(B2<>"",MIN(TODAY(),C2)-B2,"")

copy down

M.