How to subtract dates in Excel with different date formats

Question

Excel won't let me subtract two dates since their date formats are different. So, my first date was on September 18, 2011, and my second was on September 15, 2011, both in DD, MM, and YYYY format. So how do I take these two dates apart? DAY(cell of date one) - DAY(cell of date 2) will not function.

Answer 1

You can use dates in any computation if they are truly entered as dates because dates are actually saved as numeric values. There is no difference between displaying one date in "MM/DD/YY" and the other in "DD.MM.YYYY." Format the cells as "General" if you can. If the display now reads a number, such as 40801 for today, you're good to go. You might want to convert your dates if they are entered as strings first.