compare dates with different formats

G

GWC

What Excel formula can I use to determine if the date in each row in Col B is later (i.e., later) than the date in Col A?

A B
12111995 01/10/1996
11012007 01/23/2008
02282009 05/11/2009
06061989 09/13/1989

(I can't use macros or VBA)
 
C

Claus Busch

Hi,

Am Mon, 3 Dec 2012 10:46:48 -0800 (PST) schrieb GWC:
A B
12111995 01/10/1996
11012007 01/23/2008
02282009 05/11/2009
06061989 09/13/1989

try:
=B1>DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2))


Regards
Claus Busch
 
J

joeu2004

GWC said:
What Excel formula can I use to determine if the date
in each row in Col B is later (i.e., later) than the
date in Col A?
A B
12111995 01/10/1996
11012007 01/23/2008
02282009 05/11/2009
06061989 09/13/1989

Normally, the format of a date does not matter. If column A contains Excel
dates simply formatted as Custom mmddyyyy, you can simply do
IF(B1>A1,"later","not later").

However, if column A contains those values as integers (formatted as Custom
00000000?) or as strings, you will need to convert them.

IMHO, it would be best to convert all of the dates in column A. The Text To
Column operation might do the job. But IIRC, it has problems with certain
ambiguous representations, despite the leading zeros. Test or review the
results carefully.

If you do not want to convert all of the dates in place, you can do the
following:

IF(B1>--TEXT(A1,"00\/00\/0000"),"later","not later")
 

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

Top