Forum Discussion
comparing dates in 2-columns and highlighting the cell
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 differe...
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?
- 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.
