Date Comparison

SWLee

New Member
I need a formula that works for both two digit and 1 digit months. I add multiple records at a time, and the actual date can vary widely. I can use one formula and then make corrections, but would like something that works wither way. Thanks in advance!
 Column BM Column BN Column BO Actual Date Month Month Check Result 9/27/2013 2013-9 =(LEFT(BM1,1)-RIGHT(BN1,1)=0 TRUE 10/12/2013 2013-10 =(LEFT(BM1,1)-RIGHT(BN1,1)=0 FALSE

<tbody>
</tbody>

The date in Column BM is populated by a formula referring to a cell in a query - I can't modify it - always returns the 1st 9 months as a single integer and the last 3 months as a double integer
The date in Column BN is manually populated

This is used to error check the manual entries in column BN

bschwartz

Well-known Member
Does this work for you?

=LEFT(BM2,FIND("/",BM2)-1)=RIGHT(BN2,LEN(BN2)-FIND("-",BN2))

SWLee

New Member
Looks like it will work, I'll check it out at work in the morning and let you know. THANKS for the quick response.

mfexcel

Well-known Member
try:
=MONTH(BM2)=MID(BN2,FIND("-",BN2)+1,2)*1

bschwartz

Well-known Member
try:
=MONTH(BM2)=MID(BN2,FIND("-",BN2)+1,2)*1
If I'm not mistaken, column BM is text (otherwise op's LEFT function posted above would not work). Thus you would either need to combine that MONTH function with a VALUE, or else treat column BM as text.

SWLee

New Member
Sorry to take this long to get back to you. A lot going on in my world right now. I tried both of the suggested solutions, and neither one works when they hit double integer in the date. And bschwartz is absolutely correct the column BM is text. It refers to a cell in the query and I haven't been able to convert it to a date.

mfexcel

Well-known Member
Try this...
=if(find("/",bm1)=2,left(bm1,1),left(bm1,2))=if(len(bn1)=6,right(bn1,1),right(bn1,2))

and bschwartz is absolutely correct the column bm is text. It refers to a cell in the query and i haven't been able to convert it to a date.

bschwartz

Well-known Member
Strange, it works for me... Can you post the data and the results, and the specific formulas you tried?

Pup Denab

Active Member
HI SWLee
Try this on Column BO
=IF(FIND("/",A1,1)=2,LEFT(A1,1),LEFT(A1,2))=(TRIM(MID(B1,FIND("-",B1,1)+1,10)))
but before you do, make sure that the cell you put it in, is formatted as general, hopefully that should resolve the issue

SWLee

New Member
Hmm, I get a result of "FALSE" for all my dates in September and "TRUE" for all my dates in October. Let me study this a bit. - this was mfexcel's formula that I tried

1,082,281
Messages
5,364,216
Members
400,786
Latest member
ismi88

This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...