Categories
Mastering Development

VBA – sort list with start and end times that crosses the date border

I have a Excel list job batches, where each batch consists of multiple scheduled jobs. For each batch and each job a start and end time is specified. The list is sorted by start time of batches and then by start time of jobs within each batch. However, this sorting creates issues if batches and jobs stretch across the date border. Suppose the following example:

  • StartTimeBatch EndTimeBatch StartTimeJob EndTimeJob
  • 20:50 04:00 00:30 02:30
  • 20:50 04:00 02:40 03:50
  • 20:50 04:00 21:00 23:50 (sometimes crossing date border, say 00:10)

I am looking for a sorting mechanism that creates:

  • StartTimeBatch EndTimeBatch StartTimeJob EndTimeJob
  • 20:50 04:00 21:00 23:50 (sometimes crossing date border, say 00:10)
  • 20:50 04:00 00:30 02:30
  • 20:50 04:00 02:40 03:50

Essentially the mechanism should sort by start time of the job, but take into account that some jobs start on the previous day and put them first in the batch.

I tried several if-condition based loops but did not succeed. I always started with determining whether a batch or job stretches across the date border using datediff(). However, I struggle with determining the beginning and end of a batch and to sort only within the batch.

Is there any easy solution to this in vba.

Many thanks for your support!

Leave a Reply

Your email address will not be published. Required fields are marked *