convert date result from date serial to conventional mm/dd/yyyy date from conditoinal formula

=IF((AND(L16="H",H16>0)),(J16-H16+K16),IF((OR(L16="H",H16=0)),(H16+J16+K16),(IF(L16="D",(I16+DATE_TEXT(K16)),IF(L16="C",(H16+J16+K16))))))

When the user selects "d" in L16, the system needs to add a date such as 4/24/2007 in cell I16 and a number of days such as 1000 in cell K16 and display the result in K16.  Currently, the result is 16327.  It needs to display 4/24/2009.

Thus, the mechanism of DATE_TEXT does not work, and I have tried a few others - DATEVALUE ,,,  Likely, I have an error in the formula.  However, I was wondering if there is a way to format the cell - M16 in this case - on the fly.  Because, the user can select other values from L16 that do not involve dates.  Thus, changing the L16 cell from "General" to "Date" will cause a problem for the other user options.

I feel there are two choices, conver the date serial to a date or reformat the display cell on-the-fly.  Suggestions? And, thanks in advance.

Steve

I have no idea what DATE_TEXT is, it doesn't exsit on my machine, but if you want the cell in a particular formt, just format it (Menu: Format>Cells>Custom), it is only a presentational veneer.

 Bob,

Thank you for your response.  I could format the cell for a date, but there are two other conditions in the formula that has it display numbers.  I was thinking of forcing a format in the formula to convert the serial date to a mm/dd/yyyy date or forcing the cell format change conditionally.  So, that is what has me perplexed at this time.  BTW, I found DATE_TEST on line.

Thanks Again,

Steve

You could format it as text, but it won't be a date anymore, just look like one.