Calculating The Number Of Weekday Hours Between Two Dates

I used this formula, but only the time calculated. The date was ignored. Help! We only have Windows 98 and Excel XR-2. Is that the problem?

Dear Sir,

Need your valuable guidance on count commands in excel. I'm using excel 2000. Pls mail me your mail id to send you attachment of the calculations I wanted to do.

Regards,
Santosh

Yeah, you must have the Analysis TookPack installed to use this function. It's comes with Excel. Look under Tools>Add Ins.

Your friend,
HH

Problem:

Calculating the number of hours between Date1 and Date2, excluding weekends.

Solution:

Use the NETWORKDAYS function as follows:
=NETWORKDAYS(A2,B2)-1-MOD(A2,1)+MOD(B2,1)

Example:

Date1_______________Date2________________Result
4/4/2005 10:30______14/4/2005 19:12______200:42

Hi -- what should be the format for the result cells?
Also, if there are 8 WORKING days, and assuming an 8 hr day, wouldn\'t an answer of 64 hours be more useful? Maybe start and end times for the work day could be parameter cells?

Hi Andy Black,

Originally Posted by Andy Black

Hi -- what should be the format for the result cells?
Also, if there are 8 WORKING days, and assuming an 8 hr day, wouldn\'t an answer of 64 hours be more useful? Maybe start and end times for the work day could be parameter cells?

See here

http://groups.google.co.nz/groups?q=...wo+dates&hl=en

Alan.

I dont understand why this formula doesnot work on my Excel worksheet please help I am using XP standard Excel please help!

See my post monitor excess time used for a service. Maybe this is what you're looking for.

This formula is accurate about 50% of the time. I used it to calculate the hours for 160 instances and after manually verifying the calculation on some of these instances it was wrong on about half of them. For instance, the formula yielded the result of 1 hour 55 minutes for the time period between 4/10/07 1:18 PM and 4/11/07 3:13 PM. On the flip side, it was entirely accurate when calculation the hours between 11/6/06 5:15 PM and 11/7/06 9:46 AM (16 hours, 31 minutes.)

hello mjdjunk,

which formula are you using? If you have start date/time in A2 and end date/time in B2 then

=NETWORKDAYS(A2,B2)-1-MOD(A2,1)+MOD(B2,1)

should give you the correct result

format result cell as [h]:mm otherwise you'll see 1:55 instead of 25:55

A2 and B2 should not be at the weekend (if you have start or end times at the weekend then you'll need a revised formula)