Jun 14 2017
05:25 AM
- last edited on
Jul 25 2018
09:36 AM
by
TechCommunityAP
Jun 14 2017
05:25 AM
- last edited on
Jul 25 2018
09:36 AM
by
TechCommunityAP
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
Jun 14 2017 07:53 AM
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
Jun 14 2017 07:55 AM
Jun 20 2017 07:47 AM
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.
Jun 21 2017 02:29 AM
Jun 21 2017 06:02 AM - edited Jun 21 2017 06:04 AM
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.
Jun 21 2017 07:05 AM
Jun 21 2017 07:07 AM
Jun 21 2017 07:17 AM
Not exactly, - if you are dealing with periods more than 365 days...
Jun 21 2017 08:58 AM
The formula, with a little tweeking will give the required result without length of days limitation.
See attached sample