Date Comparison

SWLee

New Member
Joined
Oct 17, 2013
Messages
11
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
Joined
Jan 5, 2013
Messages
1,367
Does this work for you?

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

SWLee

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

bschwartz

Well-known Member
Joined
Jan 5, 2013
Messages
1,367
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
Joined
Oct 17, 2013
Messages
11
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
Joined
Jan 8, 2009
Messages
791
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
Joined
Jan 5, 2013
Messages
1,367
Strange, it works for me... Can you post the data and the results, and the specific formulas you tried?
 

Pup Denab

Active Member
Joined
May 12, 2013
Messages
299
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
Joined
Oct 17, 2013
Messages
11
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
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top