+ Reply to Thread
Results 1 to 3 of 3

Calculating time difference between two days capturing the overnight time.

  1. #1
    Registered User
    Join Date
    02-07-2007
    Posts
    2

    Calculating time difference between two days capturing the overnight time.

    Guys, help a blonde please :o).

    Cell A1 - 01/07/07
    Cell B1 - 3 PM
    Cell C1 - 01/08/07
    Cell D1 - 10 AM

    I need a time difference (in minutes) between these 2 days (c+d) - (a+b) that subtratcs 990 minutes of overnight (talking 7.5 working day from 9 am to 5.30 pm).

    How whould I do it keeping the data the way it's entered?


    Thanks!

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    So the answer here should be 210 (minutes), right? Assuming you want the working time.

    What would happen if you had a Friday to a Monday, I assume you'd want to exclude weekend hours too. One way to do that

    =((NETWORKDAYS(A1,C1)-1)*("17:30"-"09:00")+D1-B1)*1440

    format as general.

    note: NETWORKDAYS requires Analysis ToolPak, assumption is that start times and dates are always within the working day

  3. #3
    Registered User
    Join Date
    02-07-2007
    Posts
    2
    Thanks!

    Works perfectly well. (My boss is impressed hee hee) :o).

+ 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