Count Between dates

  • Thread starter Thread starter juancarlos
  • Start date Start date
J

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?
 
That is because your values are text strings rather than dates. Convert them
to dates first.
 
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
 
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
 
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.
Click to expand...
Try this:
1. Format the cell as a date
2. Re-enter the date.
Click to expand...
Now try your formula.
Click to expand...

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
 
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.
 
David,
I think you are right about the Windows Regional Options setting, I am using
U.S settings.
Thanks again for the feedback
Juan
 
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?
Click to expand...

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
 
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.
 
I posted a question 3 times on the general question, but I don’t see it on
the screen.
Any idea what’s wrong?
 
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.
Click to expand...
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.
Click to expand...


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
 
Back
Top