Date Comparison

[SWLee]

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]

Does this work for you?

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

 

[SWLee]

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]

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

 

[bschwartz]

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]

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]

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]

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

 

[Pup Denab]

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]

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