Difference between Dates

Hi Friends,

Please help me on the excel formula to calculate the difference between two dates shown as Days:Hours:Minutes Format. The difference should exclude the weekdends i.e., Saturdays and Sundays. I am looking for formulas in the two criteria.
Kindly help using the below sample data.

Criteria 1:

Start Date: 1/25/2007 9:28:00 PM
End Date: 1/26/2008 8:46:00 AM

Answer should be showing the difference in Days:Hours:Minutes excluding weekends.
I calculated the difference using regular Minus formula i.e., End Date - Start Date.
I got the result as 365.471 days. Then I multipled 0.471 with 24, the result is 11.304 Hours. Finally I multipled 0.304 with 60, the result is 18 Minutes. Result is in bits and pieces to be retyped as 365 days, 11 Hours and 18 Minutes i.e., 365:11:18. Still the above result is not meeting the requirement, it is not excluding weekends (Saturdays and Sundays). Any help in this regard.

Criteria 2:

Start Date: 1/25/2007 9:28:00 PM
End Date: 1/26/2008 8:46:00 AM

Office working hours: 9.30 AM to 6.3 PM.

Answer should be the difference between above dates in Days:Hours:Minutes excluding weeknds and time if the enquiry is raised other thn office hours.

I am working in an ITES industry. Customer raises an enquiry i.e, start date and time.
We close the enquiry i.e, end date and time. The difference is what is known as TAT.
There are chances that either the customer can raise a ticket on a weenend and also after the office business hours as it is raised online. Now customer claims that we are taking more time i.e., out TAT is more. I am a six sigma guy and has been given this project. Before I jump in,I want to define the problem well and state this much is the actual TAT excluding weekends and considering our office hours only. Here is where I am stucked at.
For criteria1 and criteria2 what are the formulas and how to calculate. I have taken 400 enquires to study and want to calculte for all 400. Hence looking for formulas. Please help me friends.

With regards,
venu_creative

Try NETWORKDAYS funkction

Originally Posted by zbor

Try NETWORKDAYS funkction

Hi,

Simple Network days formula gives the difference only as days.
I want in the format Days:Hours:Minutes. Formula of simple networkdays shows the difference between today 01.00 PM and yesterday 8.15 PM as one day, which is not true. I am looking for the formula giving the answer 0 days 16 hours and 45 minutes. I think there is something which we need to add with networkdays formula.

Hi Friends,

Would appreciate any help atleast for criteria1 i.,e difference between two dates in Days:Hrs:Min format excluding weekends.

Originally Posted by DonkeyOte

In the most basic terms:

Please Login or Register  to view this content.

where B2 is start datetime, C2 is end datetime - adjust to suit.

Note: additional holiday range can be added to NETWORKDAY calls as required.

Thank you very very much.

Hi Frends
instead of starting ogf new thread i starts here
i have a 1 simple question
how would i add 25 yrs in date ain correct form
I have 1 col A & B
10-Mar-08 4-Mar-33
2-Jan-08 26-Dec-32
2-Jan-08 26-Dec-32

i want in b col B1 09-mar-33, B2 01-jan-32 .....
i have thousand of col so it would very hard to change manully

hope to see reply soon
Thanx

Whaaz

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.