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

1. ## 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  Register To Reply

2. ## Re: Calculating monthly hours with split timings at the end/ beg of a month

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.  Register To Reply

3. ## Re: Calculating monthly hours with split timings at the end/ beg of a month

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.  Register To Reply

4. ## Re: Calculating monthly hours with split timings at the end/ beg of a month

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

5. ## Re: Calculating monthly hours with split timings at the end/ beg of a month

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.  Register To Reply

6. ## Re: Calculating monthly hours with split timings at the end/ beg of a month

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

Thanks a lot  Register To Reply