# SImple but Impossible Calculation

1. ## 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

2. ## 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

3. ## Re: SImple but Impossible Calculation

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

You're welcome and thanks for the rep

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