+ Reply to Thread
Results 1 to 4 of 4

Time between two dates

  1. #1
    Registered User
    Join Date
    02-16-2015
    Location
    reno, nv
    MS-Off Ver
    2010
    Posts
    2

    Time between two dates

    I am trying to put together a formula that determines the amount of work days between two dates. I only want to count Monday-Sundays, and occasionally be able to also remove holidays. However, the formula I am currently using only counts whole days, which is throwing off my accuracy. Each date has a time stamp and I am looking to calculate the days down to specifics (i.e. 1.3 days). Here's an example:

    12/2/2014 18:00 12/16/2014 1:48 formula: =SUM(INT((WEEKDAY(G14-{2,3,4,5,6,7})+H14-G14)/7))-SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX($P:$P,G14):INDEX($P:$P,H14)),$P$1412:$P$1416,0)))

    Result: 12

    Actual: 11.32

    I wouldn't mind doing the math manually except that there are thousands of these and that would be too time consuming. Thank you!

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Time between two dates

    Maybe this

    =NETWORKDAYS(G14,H14)+MOD(ABS(MOD(H14,G14)),1)
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    02-16-2015
    Location
    reno, nv
    MS-Off Ver
    2010
    Posts
    2

    Re: Time between two dates

    It's working for some of the dates, but when I get down the list, it isn't working. for example:

    12/21/2014 18:00 to 12/22/2014 7:47

    Formula output: 1.574

    Actual: .574

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Time between two dates

    Quote Originally Posted by cshrecengost View Post
    .....I only want to count Monday-Sundays.
    That's all 7 days! I assume you mean Monday to Saturday, that's what your original formula is counting.

    Can G14 or H14 be on a Sunday or a holiday? In your last example, i.e......

    Quote Originally Posted by cshrecengost View Post
    12/21/2014 18:00 to 12/22/2014 7:47
    ....that start date is a Sunday, so shouldn't you only be counting from Midnight to 7:47?

    If that's a valid example then try this formula

    =NETWORKDAYS.INTL(G14,H14,11,$P$1412:$P$1416)+NETWORKDAYS.INTL(H14,H14,11,$P$1412:$P$1416)*(MOD(H14,1)-1)-NETWORKDAYS.INTL(G14,G14,11,$P$1412:$P$1416)*MOD(G14,1)

    .....but if it's not possible for start or end date to be on a Sunday or holiday then it should be sufficient to use this version:

    =NETWORKDAYS.INTL(G14,H14,11,$P$1412:$P$1416)-1+MOD(H14,1)-MOD(G14,1)
    Audere est facere

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Finding a date/time between other dates/time
    By julesmctavish in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 10-28-2014, 12:22 AM
  2. Time Calculation (Spanning Dates and using Military Time)
    By HospitalOfficer in forum Excel General
    Replies: 5
    Last Post: 07-29-2014, 12:18 PM
  3. Dates Of Time-Chart Treated As Categories Rather Than Dates
    By RowanB in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 06-11-2013, 07:44 AM
  4. [SOLVED] Calculate Time between 2 dates and if within time frame
    By halfpint123 in forum Excel General
    Replies: 6
    Last Post: 04-11-2012, 09:45 AM
  5. Replies: 4
    Last Post: 01-25-2009, 11:25 AM

Tags for this Thread

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