days count between different date format

[David]

I try to count the # of days in each row between two columns but each field
has different format. Some cells are in Date Format and some are in Text
format. How can I setup a formula to count the number of days? I got the
#NUM! when I used =DATEDIF(A1,B1,"D")+1.

 

[Peo Sjoblom]

You need to convert the text format to real dates first.

How do the text dates look like

--


Regards,


Peo Sjoblom

 

[Ashish Mathur]

Hi,

In a spare column, use the ISNUMBER() function on the date. Then filter the
column to display all FALSE values. Now select the date column and go to
Data > Text to columns. Select the date format under "Date" radio button.
this will convert all the text values to dates.

This solution assumes that all the non dates are in one format.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[David]

the table was received from other party and I am trying to count the # of
days automatical in other column C. Even I format the column A & B to DATE,
all still keep in the original format. Like that:
ColumnA Column B
"1-1-90" 1-30-90
1-2-90 1-30-90
"1-1-90" "1-31-90"
I think the data was copied and pasted from other system to the Excel
worksheet