+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    08-13-2012
    Location
    Oman
    MS-Off Ver
    365
    Posts
    12

    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
    Attached Files Attached Files

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,207

    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: copy to clipboard
    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. #3
    Registered User
    Join Date
    08-13-2012
    Location
    Oman
    MS-Off Ver
    365
    Posts
    12

    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.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-13-2012
    Location
    Oman
    MS-Off Ver
    365
    Posts
    12

    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.
    Attached Files Attached Files

  5. #5
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,207

    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. #6
    Registered User
    Join Date
    08-13-2012
    Location
    Oman
    MS-Off Ver
    365
    Posts
    12

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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