+ Reply to Thread
Results 1 to 6 of 6

SImple but Impossible Calculation

  1. #1
    Forum Contributor
    Join Date
    01-26-2010
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    162

    SImple but Impossible Calculation

    Hi all,

    I am trying to calculate the duration that it has taken from start to finish & display that result as in
    Please Login or Register  to view this content.
    The calulation should not take into the account weekends nor holidays that are manually listed.

    I tried using Networking function but it doesn't like the time format so I am struggling to use a formula that can calculate both.

    For example:

    Date & Time start Date & Time Finish Time Taken What I would like the result to show
    09/12/2014 09:30 12/12/2014 11:30 4 Days 0 Hours 00 Mins 4 Days 2 hours 0 minutes
    09/12/2014 09:30 12/12/2014 08:30 4 Days 0 Hours 00 Mins 3 Days 23 hours 0 minutes
    24/12/2014 08:00 28/12/2014 12:00 1 Days 0 Hours 00 Mins 1 Days 4 hours 15 minutes


    Any help or advice please?

  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: SImple but Impossible Calculation

    Maybe this

    =INT(B1)-INT(A1)&" Days "&TEXT(MOD(B1,1)-MOD(A1,1),"h:mm")&" hours"

    A
    B
    C
    1
    12/9/2014 9:30
    12/12/2014 11:30
    3 Days 2:00 hours
    2
    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
    01-07-2014
    Location
    Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: SImple but Impossible Calculation

    Hope this will solve your problem. Waiting for your feedback.

    Date Diff.PNG

    =NETWORKDAYS(A1,C1,)&" Days "&(HOUR(D1-B1))&" Hours "&MINUTE(D1-B1)&" Minutes "&SECOND(D1-B1)&" Seconds"
    =NETWORKDAYS(A1,C1,)&" Days "&(HOUR(ROUND((D2-B2),0))&" Hours "&MINUTE(D2-B2)&" Minutes "&SECOND(D2-B2)&" Seconds")

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,812

    Re: SImple but Impossible Calculation

    Try
    Please Login or Register  to view this content.
    where a1:a10 contains holidays

    Custom format as d "Days" h "Hours" mm "Mins"

  5. #5
    Forum Contributor
    Join Date
    01-26-2010
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    162

    Re: SImple but Impossible Calculation

    Hi there,

    Thanks everyone for the various solutions i had them previously but didnt calculate holidays.
    Peoe Le Mokko's solution worked like a charm.

    Thank u very much

  6. #6
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,812

    Re: SImple but Impossible Calculation

    You're welcome and thanks for the rep

+ 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. simple - if x then x calculation
    By ds0919 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-05-2013, 01:53 PM
  2. What should be a simple calculation....
    By mklein in forum Excel General
    Replies: 1
    Last Post: 02-12-2009, 06:25 AM
  3. simple calculation.
    By mangesh in forum Excel General
    Replies: 3
    Last Post: 12-13-2006, 01:20 PM
  4. Simple Calculation
    By Lucifix in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-23-2005, 04:55 AM
  5. Really simple or really impossible?
    By niels007 in forum Excel General
    Replies: 4
    Last Post: 07-06-2005, 11:05 AM

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