comparing dates in 2-columns and highlighting the cell

%3CLINGO-SUB%20id%3D%22lingo-sub-77738%22%20slang%3D%22en-US%22%3Ecomparing%20dates%20in%202-columns%20and%20highlighting%20the%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-77738%22%20slang%3D%22en-US%22%3E%3CDIV%3EHi%20Everyone%2C%3C%2FDIV%3E%3CDIV%3EI%20am%20comparing%20the%20two%20Dates%20in%20the%20two%20columns%20(D%20and%20E).%3C%2FDIV%3E%3CDIV%3EThe%20Dates%20in%20column%20D%20are%20source%20Dates%20and%20the%20Dates%20in%20column%20E%20are%20Start%20date%20of%20the%20Project.%3C%2FDIV%3E%3CDIV%3EI%20am%20calculating%20the%20difference%20in%20two%20Dates%20as%20weeks%20and%20pasting%20the%20result%20in%20the%20column%20F%20and%20highlighting%20it%20accordingly.%3C%2FDIV%3E%3CDIV%3EI%20have%204%20cases%20with%20me.%3C%2FDIV%3E%3CDIV%3ECase%201%3A%26nbsp%3B%20if%20the%20sourcing%20date%20is%20%26gt%3B%204%20weeks%20of%20start%20date%20then%20the%20Status%20is%20Project%20delayed.%3C%2FDIV%3E%3CDIV%3ECase%202%3A%20If%20the%20source%20date%20is%20%26lt%3B%202%20weeks%20of%20the%20start%20date%20then%20the%20Status%20is%20Project%20on%20time%3C%2FDIV%3E%3CDIV%3Ecase%203%3A%20If%20the%20source%20date%20is%20%26lt%3B4weeks%2C%20%26gt%3B2%20weeks%20of%20the%20start%20date%20the%20Status%20is%20Project%20remaning.%3C%2FDIV%3E%3CDIV%3E%3CBR%20%2F%3EI%20have%20achieved%20the%20three%20cases.%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECase%204%3A%20there%20is%20a%20possiblity%20that%20in%20somecases%20the%20column%20E%20does%20not%20have%20any%20date%20and%20it%20is%20empty.%20in%20this%20Situation%2C%20I%20would%20like%20to%20have%20an%20if%20case%2C%20that%20says%20Project%20not%20started.%3C%2FP%3E%3CP%3EI%20tried%20it%20as%20Null%20but%2C%20i%20could%20not%20figure%20out%2C%20why%20this%20case4%20was%20not%20working.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20dateCompare()%0A%0AzLastRow%20%3D%20Range(%22D%22%20%26amp%3B%20Rows.Count).End(xlUp).Row%20%20%20%20'last%20data%20row%0A%0A'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~%0AFor%20r%20%3D%202%20To%20zLastRow%0AzWeeks%20%3D%20(Cells(r%2C%20%22E%22)%20-%20Cells(r%2C%20%22D%22))%20%2F%207%20%20%20%20%20%20%20%20'date%20difference%20in%20weeks%0A%0ASelect%20Case%20zWeeks%0ACase%20Is%20%26gt%3B%204%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20'later%20than%204%20weeks%0AzColour%20%3D%20vbRed%0AzText%20%3D%20%22Project%20delayed%20%22%20%26amp%3B%20Int(zWeeks)%20%26amp%3B%20%22%20weeks%22%0ACase%202%20To%204%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20'between%202%20and%204%20weeks%0AzColour%20%3D%20vbYellow%0AzText%20%3D%20%22Project%20ongoing%22%0ACase%20Is%20%26lt%3B%202%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20'less%20than%202%20weeks%0AzColour%20%3D%20vbGreen%0AzText%20%3D%20%22Project%20On-Time%22%0A%0ACase%20Else%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20'in%20case%20of%20duff%20data..%0AzColour%20%3D%20xlNone%0AzText%20%3D%20%22%20check%20dates%22%0AEnd%20Select%0A%0ACells(r%2C%20%22D%22).Interior.Color%20%3D%20zColour%20%20%20%20%20%20%20%20%20%20%20%20%20%20'set%20cell%20background%20colour%0ACells(r%2C%20%22F%22)%20%3D%20zText%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20'set%20project%20status%0A%0ANext%0A'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~%0A%0AEnd%20Sub%3C%2FPRE%3E%3CP%3EKindly%20help%20me%20to%20solve%20this%20issue.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ERegards%2C%3CBR%20%2F%3EMikz%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-77738%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20%26amp%3B%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-80793%22%20slang%3D%22en-US%22%3ERe%3A%20comparing%20dates%20in%202-columns%20and%20highlighting%20the%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-80793%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20formula%2C%20with%20a%20little%20tweeking%20will%20give%20the%20required%20result%20without%20length%20of%20days%20limitation.%3C%2FP%3E%3CP%3ESee%20attached%20sample%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-80607%22%20slang%3D%22en-US%22%3ERe%3A%20comparing%20dates%20in%202-columns%20and%20highlighting%20the%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-80607%22%20slang%3D%22en-US%22%3E%3CP%3ENot%20exactly%2C%20-%20if%20you%20are%20dealing%20with%20periods%20more%20than%20365%20days...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-80605%22%20slang%3D%22en-US%22%3ERe%3A%20comparing%20dates%20in%202-columns%20and%20highlighting%20the%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-80605%22%20slang%3D%22en-US%22%3ETrue..%3CBR%20%2F%3EIf%20you're%20dealing%20with%20different%20years%2C%20please%20use%20INT((E4-D4)%2F7)%20as%20stated%20by%20Jan.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-80602%22%20slang%3D%22en-US%22%3ERe%3A%20comparing%20dates%20in%202-columns%20and%20highlighting%20the%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-80602%22%20slang%3D%22en-US%22%3EYes%20I%20agree%20with%20Jan...%20The%20formula%20is%20okay%20if%20you're%20working%20within%20365%20days%20otherwise%20%22INT((E4-D4)%2F7)%20is%20a%20better%20option.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-80559%22%20slang%3D%22en-US%22%3ERe%3A%20comparing%20dates%20in%202-columns%20and%20highlighting%20the%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-80559%22%20slang%3D%22en-US%22%3E%3CP%3EJan%2C%3C%2FP%3E%3CPRE%3E%3DWEEKNUM(E4-D4)%3C%2FPRE%3E%3CP%3Eworks%20correctly%2C%20that's%20the%20difference%20between%20two%20days%20which%20is%20interpreted%20as%20week%20number%20starting%20from%20Jan%2001%2C%201900.%20Thus%20year%20changes%20doesn't%20matter.%3C%2FP%3E%3CP%3EReturns%20number%20of%20weeks%2C%20or%207%20days%20periods%2C%20starting%20from%20D4.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20one%20correction%20-%20if%20only%20your%20entire%20period%20not%20more%20than%20365%20days.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-80506%22%20slang%3D%22en-US%22%3ERe%3A%20comparing%20dates%20in%202-columns%20and%20highlighting%20the%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-80506%22%20slang%3D%22en-US%22%3EI%20think%20you%20meant%20%3DWEEKNUM(E4)-WEEKNUM(D4).%20Moreover%2C%20if%20the%20year%20changes%20you%20run%20into%20trouble%2C%20perhaps%20a%20simple%20%3DINT((E4-D4)%2F7)%20works%20better%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-80158%22%20slang%3D%22en-US%22%3ERe%3A%20comparing%20dates%20in%202-columns%20and%20highlighting%20the%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-80158%22%20slang%3D%22en-US%22%3E%3CP%3EFormula%20and%20conditional%20formatting%20will%20do%20the%20trick%20easier%20than%20VBA.%3C%2FP%3E%3CP%3EInsert%20this%20in%20Column%20F%3A%3C%2FP%3E%3CP%3E%3DWEEKNUM(E4-D4)%3C%2FP%3E%3CP%3Eand%20in%20Column%20H%20(Status)%2C%20insert%3C%2FP%3E%3CP%3E%3DIF(AND(E4%26lt%3B%26gt%3B%22%22%2CD4%26lt%3B%26gt%3B%22%22)%2CIF(F4%26gt%3B4%2C%22Project%20Delayed%22%2CIF(F4%26lt%3B2%2C%22Project%20On%20Time%22%2CIF(AND(F4%26lt%3B4%2CF4%26gt%3B2)%2C%22Project%20Ongoing%22)))%2C%22Project%20not%20Started%22)%3C%2FP%3E%3CP%3E..Then%20you%20can%20use%20condition%20formatting%20for%20the%20highlights.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-77785%22%20slang%3D%22en-US%22%3ERe%3A%20comparing%20dates%20in%202-columns%20and%20highlighting%20the%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-77785%22%20slang%3D%22en-US%22%3EI%20am%20wondering%20however%2C%20why%20are%20you%20using%20VBA%20and%20not%20a%20formula%20combined%20with%20some%20conditional%20formatting%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-77784%22%20slang%3D%22en-US%22%3ERe%3A%20comparing%20dates%20in%202-columns%20and%20highlighting%20the%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-77784%22%20slang%3D%22en-US%22%3E%3CP%3ESomething%20like%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20dateCompare()%0A%0AzLastRow%20%3D%20Range(%22D%22%20%26amp%3B%20Rows.Count).End(xlUp).Row%20%20%20%20'last%20data%20row%0A%0A'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~%0AFor%20r%20%3D%202%20To%20zLastRow%0A%0AIf%20Cells(r%2C%20%22E%22).Value%20%3D%20%22%22%20Then%0A%0AzColour%20%3D%20vbRed%0AzText%20%3D%20%22Project%20not%20started%22%0A%0AElse%0AzWeeks%20%3D%20(Cells(r%2C%20%22E%22)%20-%20Cells(r%2C%20%22D%22))%20%2F%207%20%20%20%20%20%20%20%20'date%20difference%20in%20weeks%0A%0ASelect%20Case%20zWeeks%0ACase%20Is%20%26gt%3B%204%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20'later%20than%204%20weeks%0AzColour%20%3D%20vbRed%0AzText%20%3D%20%22Project%20delayed%20%22%20%26amp%3B%20Int(zWeeks)%20%26amp%3B%20%22%20weeks%22%0ACase%202%20To%204%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20'between%202%20and%204%20weeks%0AzColour%20%3D%20vbYellow%0AzText%20%3D%20%22Project%20ongoing%22%0ACase%20Is%20%26lt%3B%202%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20'less%20than%202%20weeks%0AzColour%20%3D%20vbGreen%0AzText%20%3D%20%22Project%20On-Time%22%0A%0ACase%20Else%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20'in%20case%20of%20duff%20data..%0AzColour%20%3D%20xlNone%0AzText%20%3D%20%22%20check%20dates%22%0AEnd%20Select%0A%0ACells(r%2C%20%22D%22).Interior.Color%20%3D%20zColour%20%20%20%20%20%20%20%20%20%20%20%20%20%20'set%20cell%20background%20colour%0ACells(r%2C%20%22F%22)%20%3D%20zText%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20'set%20project%20status%0AEnd%20If%0ANext%0A'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~%0A%0AEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor
Hi Everyone,
I am comparing the two Dates in the two columns (D and E).
The Dates in column D are source Dates and the Dates in column E are Start date of the Project.
I am calculating the difference in two Dates as weeks and pasting the result in the column F and highlighting it accordingly.
I have 4 cases with me.
Case 1:  if the sourcing date is > 4 weeks of start date then the Status is Project delayed.
Case 2: If the source date is < 2 weeks of the start date then the Status is Project on time
case 3: If the source date is <4weeks, >2 weeks of the start date the Status is Project remaning.

I have achieved the three cases.

 

Case 4: there is a possiblity that in somecases the column E does not have any date and it is empty. in this Situation, I would like to have an if case, that says Project not started.

I tried it as Null but, i could not figure out, why this case4 was not working.

 

Sub dateCompare()

zLastRow = Range("D" & Rows.Count).End(xlUp).Row    'last data row

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For r = 2 To zLastRow
zWeeks = (Cells(r, "E") - Cells(r, "D")) / 7        'date difference in weeks

Select Case zWeeks
Case Is > 4                                         'later than 4 weeks
zColour = vbRed
zText = "Project delayed " & Int(zWeeks) & " weeks"
Case 2 To 4                                         'between 2 and 4 weeks
zColour = vbYellow
zText = "Project ongoing"
Case Is < 2                                         'less than 2 weeks
zColour = vbGreen
zText = "Project On-Time"

Case Else                                           'in case of duff data..
zColour = xlNone
zText = " check dates"
End Select

Cells(r, "D").Interior.Color = zColour              'set cell background colour
Cells(r, "F") = zText                               'set project status

Next
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

End Sub

Kindly help me to solve this issue.


Regards,
Mikz

9 Replies

Something like:

 

Sub dateCompare()

zLastRow = Range("D" & Rows.Count).End(xlUp).Row    'last data row

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For r = 2 To zLastRow

If Cells(r, "E").Value = "" Then

zColour = vbRed
zText = "Project not started"

Else
zWeeks = (Cells(r, "E") - Cells(r, "D")) / 7        'date difference in weeks

Select Case zWeeks
Case Is > 4                                         'later than 4 weeks
zColour = vbRed
zText = "Project delayed " & Int(zWeeks) & " weeks"
Case 2 To 4                                         'between 2 and 4 weeks
zColour = vbYellow
zText = "Project ongoing"
Case Is < 2                                         'less than 2 weeks
zColour = vbGreen
zText = "Project On-Time"

Case Else                                           'in case of duff data..
zColour = xlNone
zText = " check dates"
End Select

Cells(r, "D").Interior.Color = zColour              'set cell background colour
Cells(r, "F") = zText                               'set project status
End If
Next
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

End Sub

 

 

I am wondering however, why are you using VBA and not a formula combined with some conditional formatting?

Formula and conditional formatting will do the trick easier than VBA.

Insert this in Column F:

=WEEKNUM(E4-D4)

and in Column H (Status), insert

=IF(AND(E4<>"",D4<>""),IF(F4>4,"Project Delayed",IF(F4<2,"Project On Time",IF(AND(F4<4,F4>2),"Project Ongoing"))),"Project not Started")

..Then you can use condition formatting for the highlights.

I think you meant =WEEKNUM(E4)-WEEKNUM(D4). Moreover, if the year changes you run into trouble, perhaps a simple =INT((E4-D4)/7) works better?

Jan,

=WEEKNUM(E4-D4)

works correctly, that's the difference between two days which is interpreted as week number starting from Jan 01, 1900. Thus year changes doesn't matter.

Returns number of weeks, or 7 days periods, starting from D4.

 

With one correction - if only your entire period not more than 365 days.

 

 

Yes I agree with Jan... The formula is okay if you're working within 365 days otherwise "INT((E4-D4)/7) is a better option.
True..
If you're dealing with different years, please use INT((E4-D4)/7) as stated by Jan.

Not exactly, - if you are dealing with periods more than 365 days...

The formula, with a little tweeking will give the required result without length of days limitation.

See attached sample