Break a Block of Time Out Into Separate Months

I have columns of dates and times that fleet vehicles have been taken out of service (OOS), and a second set of columns in which the vehicles have returned back into service (INS). I am trying to build a monthly rollup that calculates the total time that any given vehicle was OOS during each month.

The problem I have is that vehicles are often OOS during timeframes that span multiple months. ARFF-14, as a good example, was OOS for 98 days, 4 hrs., and 1 min. Here are the cells:

Vehicle Date OOS Time OOS Date INS Time INS
ARFF 14 2019-02-25 13:20 2019-06-03 17:21

As you can see, the OOS time spans five months: Feb, Mar, Apr, May, and Jun.

I need to build VBA code that breaks those 98.16736111 days out into separate months: 3.24 days for Feb, 31.00 days for March…all the way out to 2.72 days for June.

What is the best way to build this code, taking into account leap years and date/time ranges that often fall within one month, but occasionally can be much longer?

Thank you very much in advance!

Mark Burns
Win10 OS, Excel2010.

By: Mark Burns

One thought on “Break a Block of Time Out Into Separate Months”

1. Mark

Solved without VBA. Implemented a very complex IF…THEN formula to evaluate the dates. I used “place holder” cells (which I usually prefer not to do). Formula looks like this:

=IF(\$Z3=””,””,IF(AND(MONTH(\$B3)=AA\$2,MONTH(\$D3)=AA\$2),(\$D3+\$E3)-(\$B3+\$C3),IF(MONTH(\$B3)=AA\$2,(DAY(DATE(YEAR(\$B3),MONTH(\$B3)+1,1)-1))-((DAY(\$B3)+\$C3)-1),IF(AND(AA\$2>MONTH(\$B3),AA\$2<MONTH(\$D3)),\$Z3-((\$D3+\$E3)-VALUE(DATE(YEAR(\$B3),AA\$2+1,1))+SUM(Z3:\$AA3)),IF(MONTH(\$D3)=AA\$2,\$Z3-SUM(Z3:\$AA3),"")))))