Free Microsoft Excel 2013 Quick Reference

Compare two dates in excel macro

Hi,
I get one date from excel cell and another date which is concatenation DD MM and YYYY. When I am comparing both the dates the first value is taken as DD/MM/YYYY and the second value is taken as "DD/MM/YYYY" as it is a concatenated value. Hence my IF statement is failing. Can anyone help solving this.

Thank you.


Post your answer or comment

comments powered by Disqus
Hi All

I am getting a problem to compare two dates in excel. One date is in excel cell and the other is in combobox in the userform.The code I have written is :

Private Sub CommandButton1_Click()
Set ws1 = Worksheets("sheet1")
res = Application.Match(Me.ComboBox1.Value, _
Worksheets("sheet1").Range("a:a"), 0)
dd = ws1.Cells(res, 3).Value
TextBox1.Value = ComboBox2.Value + "/" + ComboBox3.Value + "/" + ComboBox4.Value

If CheckIfdate(dd, TextBox1, "Date Processed") Then Exit Sub
end sub

Private Function CheckIfdate(controlname, controlvalue, controltext) As Boolean

CheckIfdate = False 'Default Value

'controlname.SetFocus
If DateValue(controlname) > DateValue(controlvalue) Then

CheckIfdate = True

'ComboBox1.BackColor = &HFF&
'ComboBox2.BackColor = &HFF&
' ComboBox3.BackColor = &HFF&

MsgBox "Invalid date: Date Processed can't be less than Date Received", vbCritical

End If

End Function I tried the other methods too like date format functions etc. but no use.Could anyone help me to solve this problem.

Regards
Aman

Hi,

I need to compare dates in excel to find delayed orders but the only results i get is something like 1900-01-15, which is correct but I need a format saying 15 days or in case of longer delays 1 month 10 days and so on.

Today I compare the numbers by using this formula =K2-L2 (2007-05-13-2007-04-28)

Thank you for any help you could give me and forgive me for my incompetence.

Dave :o

Hi All,

I'm not sure if this is possible but what I would like to do is compare two columns for a best match.

The set of columns contain names from two different sources. They are not an exact perfect match so vlookup or match will miss out names that are virtually the same but not an exact match (according to Excel). I could also use the vlookup with the true parameter (with columns sorted) but this never even approximates a good match.

Is there are way that I can compare two columns and find out how close the match is in Excel? For example:

The names A J Johnson and Andrew J Johnson are the same peoples but trying to cross match with Excel is difficult. What I would like Excel to output is Andrew J Johnson being the closest match to A J Johnson (which Excel doesn't return). Or is Access the best for doing something like this? I've racked my head about this...

Rgds,

CS

Hi. Thanks for the warm welcome. I am under a time constraint for a project I'm working on. I need to compare two columns in two sheets (in the same workbook) ---the data is a date and time in format 6/12/2010 4:00PM.

Both reside in column A (sheet2 and sheet3) - the columns may not have the same ammount of data, but I guess the best way to do it is loop(or recurse) until it finds blank or an N/A. I I have looked through your forum and have seen similar posts-- but nothing that quite matches what I am looking for.

If the data matches -- I need it pasted in ASCENDING order (6/1/2010 11:00AM ----- 6/2/2010 12:00PM) into 'Sheet 1'. I will move the two sheets of data into the workbook (before running the macro) and they will be named Sheet2 and Sheet3.

Once again the order will not be identical, so it is important that what you write can look up and down both columns , copy the matches, then paste them in ascending order.

Any help would be appreciated.

Yes.... I am a n00b - but atleast one that can admit it and wants to learn. Comments on the code would be much appreciated.

Hi All,

I have a below macro which compares the ranges from two different worksheet. I want this macro to check / compare ranges in each sheet between two workbooks and then give a summary tab whereever it doesn't match.

Sub CompareWorksheetRanges(rng1 As Range, rng2 As Range)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
    If rng1 Is Nothing Or rng2 Is Nothing Then Exit Sub
    If rng1.Areas.Count > 1 Or rng2.Areas.Count > 1 Then
        MsgBox "Can't compare multiple selections!", _
            vbExclamation, "Compare Worksheet Ranges"
        Exit Sub
    End If
    Application.ScreenUpdating = False
    Application.StatusBar = "Creating the report..."
    Set rptWB = Workbooks.Add
    Application.DisplayAlerts = False
    While Worksheets.Count > 1
        Worksheets(2).Delete
    Wend
    Application.DisplayAlerts = True
    With rng1
        lr1 = .Rows.Count
        lc1 = .Columns.Count
    End With
    With rng2
        lr2 = .Rows.Count
        lc2 = .Columns.Count
    End With
    maxR = lr1
    maxC = lc1
    If maxR < lr2 Then maxR = lr2
    If maxC < lc2 Then maxC = lc2
    If lr1 <> lr2 Or lc1 <> lc2 Then
        If MsgBox("The two ranges you want to compare are of different size!" & _
            Chr(13) & "Do you want to continue anyway?", _
            vbQuestion + vbYesNo, "Compare Worksheet Ranges") = vbNo Then Exit Sub
    End If
    DiffCount = 0
    For c = 1 To maxC
        Application.StatusBar = "Comparing cells " & _
            Format(c / maxC, "0 %") & "..."
        For r = 1 To maxR
            cf1 = ""
            cf2 = ""
            On Error Resume Next
            cf1 = rng1.Cells(r, c).FormulaLocal
            cf2 = rng2.Cells(r, c).FormulaLocal
            On Error GoTo 0
            If cf1 <> cf2 Then
                DiffCount = DiffCount + 1
                Cells(r, c).Formula = "'" & cf1 & " <> " & cf2
            End If
        Next r
    Next c
    Application.StatusBar = "Formatting the report..."
    With Range(Cells(1, 1), Cells(maxR, maxC))
        .Interior.ColorIndex = 19
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlHairline
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlHairline
        End With
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlHairline
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlHairline
        End With
        On Error Resume Next
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .Weight = xlHairline
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .Weight = xlHairline
        End With
        On Error GoTo 0
    End With
    Columns("A:IV").ColumnWidth = 20
    rptWB.Saved = True
    If DiffCount = 0 Then
        rptWB.Close False
    End If
    Set rptWB = Nothing
    Application.StatusBar = False
    Application.ScreenUpdating = True
    MsgBox DiffCount & " cells contain different formulas!", _
        vbInformation, "Compare Worksheet Ranges"
End Sub

This example macro shows how to use the macro above:

Sub TestCompareWorksheetRanges()
    ' compare two ranges in the active worksheet in the active workbook
    CompareWorksheetRanges Range("A1:A100"), Range("B1:B100")
    ' compare two ranges in two different worksheets in the active workbook
    CompareWorksheetRanges Worksheets(1).Range("A1:A100"), _
        Worksheets(2).Range("B1:B100")
    ' compare two ranges in two different worksheets in two different workbooks
    CompareWorksheetRanges ActiveWorkbook.Worksheets(1).Range("A1:A100"), _
        Workbooks("WorkBookName.xls").Worksheets(1).Range("B1:B100")
End Sub

Thanks a lot for your help in advance.

I have two lists of partnumbers in excel

1. All active partnumbers (in column B of the worksheet"All_partnumbers")
2. Active Partnumbers with a active linkage in BOM.(in column B of the
worsheet "BOM_linkage_num")

All partnumbers are formatted as text. (some of the partnumbers are purely
numerical, some are alpha numeric and some starts with "0", hence formatted
as text)

I would like to compare list 1 with list 2 and excel to format the
partnumber in list 1 to Bold if that partnumber has an existence in list 2.

Help please!

Regards,

Jimmy Joseph

I have two dates in YYYYMMDD format in Excel. How can calculate number of
days between the two dates?

Howdy,

I'm a little stuck, dates and times are not my strength in excel

I have two cells in excel

A1 = 16/02/2011 16:14
B1 = 16/02/2011 16:33

Is it possible to work out the following:

1. How many minutes difference are between these two
2. Then to find matches where they are within say a 20 minute window of each other?

Many thanks,

Matthew

Hi there,

I have along list of dates over a 5 year financial year period (2003 - 2008) in Excel. I'm trying to code the dates by financial year from 1-5 respectively (03-04 = 1; 04-05=2 etc). E.g 6/9/2005 should be coded as 2, i.e between 7/1/04 and 30/6/05 financial year period.

My code is below but it is coming up with errors. 'A4' is the first cell of my column date range. The rest is me trying to do the above.

I'm not sure how to fix it because I'm a novice at Excel. Any suggestions? Thanks!

=IF(((A4>"7/1/2003"+0)*(A4"7/1/2004"+0)*(A4"7/1/2005"+0)*(A4"7/1/2006"+0)*(A4"7/1/2007"+0)*(A4

I want to know in cell E if Cell B2 a date field formated dd/mmm/yy is < cell M2 which is also a Date filed formatted dd/mmm/yy. I tried IF(M2

How can you compare two tables in excel to identify matches and no matches
using formulas. For instance both tables have the same information. The
only unique information would be first name and last name but different
cities. Any help will be greatly appreciated. Thank you.

I have a macro which compares two columns in a sheet.

A B
1 1
2 2
#N/A 3

A=cellA.value
B=cellA.offset(0,1).Value

if A=B then
// do something
end if
This code is throwing me an error when cell A3 is being processed.How do i overcome this error and return a false for the match ?

If you want to add a week to a date, just use =A1+7.
If you want to determine the number of weeks between two dates, just use
=(B1-A1)/7

*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com

"Linda" <Linda@discussions.microsoft.com> wrote in message
news:18449258-05DB-4087-B9D1-219C5D699052@microsoft.com...
> I need to figure out how to calculate future dates in excel in # of weeks.
> Is there an easy way to do this?

I'm using Excel 2002 and i would like to calculate the difference between two
dates in days not in workdays. Unfortunately the help only give you how to
do it in workdays. Thank you for your help

I'm running Excel 2003. I'm trying to compare two lists that don't match
exactly. I've been using Vlookup, but if the cell content of my search is
not the same as the array I'm comparing it to, it does not find it.
For example: I'm searching for "Mary Smith". The array contains "Smith,
Mary". VLookup won't find the record because it doesn't match exactly what
I'm searching for. Because I'm comparing thousands of records against an
extremely large database, it's not feasable for me to just do a search.
Any insight?

Thanks,
--
Tina

I have two worksheets: W1 and W2.

1. W1 has data in Column A, while W2 has none in that column (except for the header row of course).
2. W1 has rows that W2 does not have, and vice-versa.

I need code that will compare two columns in these two sheets. If the data in these two columns for a particular row match between the two worksheets, then for that row, I need the macro to copy the column A cell in W1 to the corresponding column A cell in W2. Finally, I would like an 'x' be placed into a W1 column if a match was found for that row.

The two sheets do not have the same number of rows, but the data being compared in the two columns should combine to form a unique row match between the two worksheets.

Thanks in advance to anyone who can help.

Hello,
Does anyone know how to set up a date in a Macro so that it allows me to put in a date when I run the Macro. When I created the macro I created it with a criteria for a September but at the end of month I will need to capture the data for October and so on. Is there a way to change the dates other than manually going into the macro every month and changing them? Listed below is the structure of the dates using the Macro recorder:

Selection.AutoFilter Field:=37, Criteria1:=">=9/5/2005", Operator:=xlAnd _
, Criteria2:="<=10/2/2005"

How should I change the code so that it look at a new set of dates every month?

Any help is appreciated.

Hi,

How to compare two dates in excel.Like my requirement is I need to compare curent date vs from month begin date two weeks date.As of now I have tried like this.But no luck on that.seeking help from all of you..

Current Date : =TEXT(NOW(),"d")

Month Begindate : =TEXT(DATE(YEAR(NOW()),MONTH(NOW()),1)+14,"d")

And I have written condition like this : =IF(TEXT(NOW(),"d")<TEXT(DATE(YEAR(NOW()),MONTH(NOW()),1)+14,"d"),1,0)

Hope I am clear.

Regards,

Siva

I'm fairly new to IF formulas and looking for help.

I need to have IF compare two dates in different columns and output the greater of the two and add 5 workdays to the greater date.

Both source dates are in the same sheet (Columns P and AA) output goes into Column AJ.

how to compare two fields in two differrent spreadsheets?

Hello

I was wondering is there a way to create a relationship between two cells in excel. I notice some people might use concatenate? I want to have the same D1 referring to H1. If D1 is moved to E12 i want to offset H1 to I12

Hi All,

I have two different days in two different coloumns in the same worksheet and I want to put the difference between these two dates in an adjacent coloumn in the following format:-

Stat DateStop DateDifferenceFormar Reguired4/1/07 10:204/3/08 5:50367.8125# Days # Hours # Minutes

I need the Difference in the above mentioned break up in days, hours and mins.

Can anyone help?

Thanks in advance.
Sandan

I need to know how to create a formula to count the number of days between two dates in excel. For example I want to know how many days are between 6/28/2007 and 7/15/2007. Any help is greatly appreciated.

i'm setting dates in excel for a chart and wish to have these dates set off
an alarm or reminder similiar to that in outlook. would love to link the 2
programs, but can not discover a way..

any help would be greatly appreciated!!!!!
--
gcarch


No luck finding an answer? You could always try Google.