Compare Dates to see if date 1 occurs before or after date 2

**ashton** · 10-10-2009, 09:45 AM

Hello All,

I have Case or Incident Numbers in Column A. Column B has log created dates. Column C has log closed dates.

End result will be the number of cases that do not have repeat logs.

I need to find duplicate case numbers and then compare the logs. If there is not a duplicate case number, Column D needs to say No (it says original in my sample for visual purposes). If there is a duplicate case number, then it needs to look at the first log closed date and look at the duplicate log and decide if it was created before or after that activity closed. If it is before it does not meet my criteria, the log must be closed. If it is after, a simple yes or repeat would work.

I put an array in column E to find the max date for the case number but i think VBA is going to be the way to get this done. In my sample I have included 21 lines however my data contains over 600,000 rows of data.

I thought the max column would help me but I need it to look from the row down.

Case 1

1st Log Created 8/01/2009 08:00 AM Log Closed 8/01/2009 10:00 AM
2nd Log Created 8/01/2009 11:00 AM Log Closed 8/10/2009 10:00 AM
3rd Log Created 8/03/2009 08:00 AM Log Closed 8/05/2009 10:00 AM
4th Log Created 8/12/2009 08:00 AM Log Closed 8/15/2009 10:00 AM

So, first log is the original. 2nd log would be a repeat. 3rd log cannot be a repeat because the 2nd is still open. 4th log would be a repeat because the second log closed.

Hope that is clear enough. Any help appreciated. Thanks

**NBVC** · 10-10-2009, 09:33 PM

Try this formula in D2, copied down:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER

Edit: I read your post more thoroughly and realized I hadn't included your conditions for your example above...

**ashton** · 10-11-2009, 11:44 PM

it's getting closer .. i stared at it all saturday and couldn't get closer .. what condition did it not meet?

**NBVC** · 10-12-2009, 08:59 AM

You'll have to elaborate on that... where is it not working?

Did you confirm the formula with CTRL+SHIFT+ENTER keys? Not just ENTER, so that { } brackets appeared around it... then copy down.