Count Between dates

[juancarlos]

How can I count between two dates when format for the dates is DD-MM-YY

I need to know how many days are between 21/04/09 and 28/04/09

The solution should be 8 if I use =(A2-A1+1) but I’m getting #VALUE!

I’m used format => Custom => dd/mm/yy, also General and Number but they are
not working either.

Any suggestions?

 

[Gary''s Student]

That is because your values are text strings rather than dates. Convert them
to dates first.

 

[Juan Carlos]

Gary,
I converted them to dates but I got the same result.

Juan

 

[Juan Carlos]

David,
I’m getting false for =ISNUMBER(A1)(A2) and True for =ISTEXT(A1)(A2). I
think my problem have to do with how the dates are entered. I don’t think
that excel recognized the format dd/mm/yy to calculate number of days between
dates.

I’m going to convert the A1 and A2 to dates by adding 2 more columns using
the following formula =DATE(RIGHT(D60,2)+2000,MID(D60,4,2),LEFT(D60,2)) and
then will do the calculation from there.

Unless you have another solution for me

Juan



Juan

 

[Fred Smith]

Excel has no trouble recognizing dd/mm/yy as a date. It doesn't require 4
digits for the year.

If you entered the date, then the likely problem is that your cell was
formatted as text beforehand.

Try this:
1. Format the cell as a date
2. Re-enter the date.

Now try your formula.

Regards,
Fred

 

[James Silverton]

Fred wrote on Thu, 24 Sep 2009 13:58:01 -0600:

If you entered the date, then the likely problem is that your cell was
formatted as text beforehand.

Try this:
1. Format the cell as a date
2. Re-enter the date.

Now try your formula.

Is the problem perhaps using British date format and US Excel. I just
tried it with two dates, one pair, British dd/mm/yy, gave #VALUE in
DATEDIF. Another pair, US format, mm/dd/yy, worked (EXCEL 2002).
--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not

 

[Fred Smith]

The Isnumber and Istext results have nothing to do with US or UK date
formats. The problem is the cell is formatted as text.

What formula did you use to get a #Value error in Datedif?

Regards,
Fred.

 

[Juan Carlos]

David,
I think you are right about the Windows Regional Options setting, I am using
U.S settings.
Thanks again for the feedback
Juan

 

[James Silverton]

Fred wrote on Fri, 25 Sep 2009 09:13:42 -0600:
..

The Isnumber and Istext results have nothing to do with US or UK date
formats. The problem is the cell is formatted as text.

What formula did you use to get a #Value error in Datedif?

I had in column A: 5/9/2009, 9/25/2009, 9/5/2009, 25/9/2009

=DATEDIF(A1,A2,"d") gave the correct answer.
=DATEDIF(A3, A4,"d") gave the error.
--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not

 

[Fred Smith]

Your value error is because A4 is text. Anything that Excel does not
recognize as a valid date is considered text.

Excel takes its input date format from Windows' Regional Settings.
Formatting a cell in Excel affects only the output (how it's displayed). No
matter how the date is formatted in Excel, inputting a date is controlled by
the Windows setting.

Regards,
Fred.

 

[Juan Carlos]

I posted a question 3 times on the general question, but I don’t see it on
the screen.
Any idea what’s wrong?

 

[James Silverton]

Fred wrote on Fri, 25 Sep 2009 14:56:21 -0600:

Excel takes its input date format from Windows' Regional
Settings. Formatting a cell in Excel affects only the output
(how it's displayed). No matter how the date is formatted in
Excel, inputting a date is controlled by the Windows setting.


Your value error is because A4 is text. Anything that Excel does not
recognize as a valid date is considered text.

Excel takes its input date format from Windows' Regional Settings.
Formatting a cell in Excel affects only the output (how it's
displayed). No matter how the date is formatted in Excel, inputting a
date is controlled by he Windows setting.

I don't get this! As i said originally, A4 is only not a date if you are
using the US convention with month/day/year.




--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not