compare dates with different formats

  • Thread starter Thread starter GWC
  • Start date Start date
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)
 
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
Click to expand...

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


Regards
Claus Busch
 
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
Click to expand...

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