Calculating monthly hours with split timings at the end/ beg of a month

I have a sheet having dates, start and end time of duty. the table automatically calculates the time difference to get total duty overlapping between two consecutive dates. I want a summary of total hours in each calendar month taking in consideration that at end/beginning of months in some cases the total has to be split between months at midnight. Attached is a spreadsheet with the desired results and notes in the total monthly cells to explain the values to be considered.

Thanks

the values in Columns B & C are a mix of time only and date time, and in some cases the date embedded in the date time values in Column B do not match the date in Column A (eg. rows 2-4)

for the purposes of the below I've assumed the "time" entries are, in reality, just time and that the mix of sample values is incorrect

Formula:

Please Login or Register  to view this content.

the above would replicate your expected results
if the values in B & C are either all datetime, or a mix of datetime + time, post back.

You are correct regarding the mix of date and time.I have changed the values in B and C to be time only. That was a mistake as figures were copied from somewhere else. I did copy paste the formula you provided and dragged it down for the other months but it returned wrong values. Spreadsheet attached. I might be doing something else wrong.

Sorry Correct totals summary is wrong. Should be Feb(10:31) Mar(50:16) Apr(4:46). Attached is amended spreadsheet.

the formula is correct however, you need to commit as an Array using CTRL + SHIFT + ENTER, per instructions in post #2

once the array is set correctly the formula will appear encased within { } -- these cannot be typed manually.

Didn't have a clue about the array thing. Now its perfect.

Thanks a lot

Hello again,

The formula when applied to a table as attached produces incorrect results. At first it was working smoothly. I changed the reference and kept comparing the original formula sent by you and couldn't find where I went wrong.

In this latest example your time values are cumulative, e.g. I11 whilst displaying 00:35 actually stores 24:35.

Without knowing if the above is going to be applied consistently I'd suggest simply inserting a MOD into relevant sections given that would cater for either / or scenario:

Formula:

Please Login or Register  to view this content.