Formula for Dates

  • Thread starter Thread starter Daren
  • Start date Start date
D

Daren

Hello,

I have four sets of dates that are supposed to occur in order. They are
formatted as the 3/14/2001 format in Excel. Column A has a start date,
column B has an intermediate date, column C has another intermediate date,
and column D has an end date. These are supposed to occur in order, but
necessarily do not. For example, a correct progression would be 1/31/2008
then 5/16/2008 then 10/1/2008 then 10/1/2011. Working with the OR function
for these dates it would return a true value, and it does. However, I need a
function to determine when any of the dates are out of order. That would be,
for example, the first intermediate date occurring before the start date.
Essentially, I need a function that would look at these four dates and return
a value of false when there are any of them that are out of order.

Thanks!
 
Hi John,

For these dates, 03/12/2007 09/19/2007 01/12/2008 01/11/2011
which occur in order, the AND function returns FALSE, but it should be true.
Is it due to the years spanning muliple years? How can I correct the
function to make it run more smoothly? Thanks!
 
Mike,

For dates in order of 03/14/2007 06/12/2007 07/23/2007 07/22/2010, the
formula you gave =AND(A1<>"",B1>A1,C1>B1,D1>C1) returned a value of FALSE,
even though it should be TRUE. What could be the problem?
Thanks!
 
How are these dates arrived at? Have you checked your regional settings to
ensure you are in the proper date format? If these are 'text' versions, there
may be a problem there. You could try this:
=AND(--D2>--C2,--C2>--B2,--B2>--A2)
 
Daren,

Not on my machine it doesn't. I would suggest you check your dates are
really dates and not text that looks like dates.

Try this
=isnumber(a1)
and drag right for b1 etc. All should return TRUE for dates.

Mike
 
The dates are formatted as 3/14/2001. The regional settings are US. I tried
your formula of =AND(--D2>--C2,--C2>--B2,--B2>--A2), but this did not work
for dates spanning multiple years. Do you know what might be the issue?

Thanks!
 
Check Mike's second post. Both formulas work for me. Are you in US? Have you
checked your regional settings to ensure US?
Building on Mike's suggestion, you could type this in the cell below your
first date (assuming in A2)
=TEXT(A2,"MMMM DD YYYY") ... and copy across to column D. This should modify
your "dates" like so:
MARCH 12 2007 SEPTEMBER 19 2007 JANUARY 12 2008 JANUARY 11 2011
If not, then there is a problem with how these dates are entered/formulated
in your spreadsheet.
 
Hello,

I have four sets of dates that are supposed to occur in order. They are
formatted as the 3/14/2001 format in Excel. Column A has a start date,
column B has an intermediate date, column C has another intermediate date,
and column D has an end date. These are supposed to occur in order, but
necessarily do not. For example, a correct progression would be 1/31/2008
then 5/16/2008 then 10/1/2008 then 10/1/2011. Working with the OR function
for these dates it would return a true value, and it does. However, I need a
function to determine when any of the dates are out of order. That would be,
for example, the first intermediate date occurring before the start date.
Essentially, I need a function that would look at these four dates and return
a value of false when there are any of them that are out of order.

Thanks!
Click to expand...

Try:

=AND(SMALL(A1:D1,{1,2,3,4})=A1:D1)

entered with <ctrl><shift><enter> as an array formula. If you do this
correctly, XL will place braces {...} around the formula.
--ron
 
Daren said:
Hello,

I have four sets of dates that are supposed to occur in order. They are
formatted as the 3/14/2001 format in Excel. Column A has a start date,
column B has an intermediate date, column C has another intermediate date,
and column D has an end date. These are supposed to occur in order, but
necessarily do not. For example, a correct progression would be 1/31/2008
then 5/16/2008 then 10/1/2008 then 10/1/2011. Working with the OR function
for these dates it would return a true value, and it does. However, I need a
function to determine when any of the dates are out of order. That would be,
for example, the first intermediate date occurring before the start date.
Essentially, I need a function that would look at these four dates and return
a value of false when there are any of them that are out of order.

Thanks!
Click to expand...

Another possible solution...with dates in A2:D2 put the following in E2 and
array-enter (CTRL+SHIFT+ENTER):

=SUM(IF(RANK(A2:D2,A2:D2,1)=COLUMN(A2:D2),1,0))=COLUMNS(A2:D2)

This can be expanded to any number of dates just by increasing the ranges or
inserting columns.
 
Daren,

The only reason either of the 2 formula you have been given ( and I note you
now have some more) would fail is if your dates aren't dates. Check them
again.

Mike
 
Ron said:
Try:

=AND(SMALL(A1:D1,{1,2,3,4})=A1:D1)

entered with <ctrl><shift><enter> as an array formula. If you do this
correctly, XL will place braces {...} around the formula.
--ron
Click to expand...


That's what I was looking for...with a slight change to make it expandable, if
needed:

{=AND(SMALL(A1:D1,COLUMN(A1:D1))=A1:D1)}
 
Try using the AND function:

=AND(A1<B1,B1<C1,C1<D1)

Adjust the cell references as required, and change the "<" operator to
"<=" if equal dates are to be allowed.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
That's what I was looking for...with a slight change to make it expandable, if
needed:

{=AND(SMALL(A1:D1,COLUMN(A1:D1))=A1:D1)}
Click to expand...


There are some other ways to make it "auto-expand":

=AND(SMALL(rng,ROW(INDIRECT("1:"&COLUMNS(rng))))=TRANSPOSE(rng))
--ron
 
There are some other ways to make it "auto-expand":

=AND(SMALL(rng,ROW(INDIRECT("1:"&COLUMNS(rng))))=TRANSPOSE(rng))
--ron
Click to expand...


or:

=AND(SMALL(OFFSET(A1,0,0,1,COUNT(1:1)),ROW(INDIRECT("1:"&COUNT(1:1))))=TRANSPOSE(OFFSET(A1,0,0,1,COUNT(1:1))))

--ron
 
Might as well add my 2 cents, for a completely different approach without an
array entry

=SUMPRODUCT((A1:C1-B1:D1<0)*(A1:C1>0))=3

or expandable

=SUMPRODUCT(--(A1:C1-B1:D1<0),--(A1:C1>0))=COUNT(A1:C1)
 
A little simpler still

=SUMPRODUCT(--(A1:C1-B1:D1<0))=3

or

=SUMPRODUCT(--(A1:C1-B1:D1<0))=COUNT(A1:C1)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel PRICE function: How does it compute Issue Date of the Security 5
Date Sequence 2 7
Count Unique Values in 1 Column based on Date Range in another Column 28
Excel Average dates help 0
IF Statement for Dates 12
countifs Function not giving desired result with date criteria 2
Dates 3
Count if dates meet certain criteria 10

Back
Top