# 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

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.

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.

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.

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.

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1