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

 

[David Biddulph]

If you are getting a #VALUE! error I'd be willing to bet that you haven't
changed them from text to dates.
If they are dates, =ISNUMBER(A1) and =ISNUMBER(A2) will return TRUE, and
=ISTEXT(A1) and =ISTEXT(A2) will return FALSE.
Perhaps you'd care to tell us what those formulae do return?

 

[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.

 

[David Biddulph]

The problem is not that the cell is *formatted* as text; the problem is
that the cell *contents* are text.

Cell formatting governs how a number is displayed, but the reason for the
#VALUE! error is that the contents are not a number but text.
The formula that gave the original #VALUE! error was given at the start of
the thread and was =(A2-A1+1)

There are a number of possible reasons for the content being text, and use
of the wrong Windows Regional Options could be one of them. If the options
are set to expect UK date and one types in 09/25/09, the cell contents will
be treated as text (and would thus give a #VALUE! error in the DATEDIF
situation which James reported, or in the original subtraction formula). If
the number typed in is 09/03/09, then it wouldn't result in text but would
merely be interpreted as 9th March instead of 3rd September.

Another possibility for the reason for the cell contents being text is the
cell being formatted as text before the number is typed in, as you mentioned
in your previous post. Another possibility is that the data (perhaps
imported from another application) includes spaces or non-breaking spaces.

 

[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

 

[David Biddulph]

You appear to be using Microsoft's web interface to the newsgroup, and it's
known to be unreliable.

You'll be better off accessing the newsgroup directly through a news server,
or alternatively there is a good searchable archive through Google, which
will also allow you to post.

Newsgroup hints:
a.. http://www.cpearson.com/excel/HintsAndTipsForNewsgroupUsers.aspx
b.. http://groups.google.com/group/microsoft.public.excel.misc

--
David Biddulph

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

....