Calculate the Days

[migdad]

Friends,
I am looking for formula to calculate the dates between two days (excluding the weekends)
I am working in Saudi Arabia and the weekend days over here are thursday and Friday.

[RMTP]

NETWORKDAYS()

"migdad" wrote:

>
> Friends,
> I am looking for formula to calculate the dates between two days
> (excluding the weekends)
> I am working in Saudi Arabia and the weekend days over here are
> thursday and Friday.
>
>
> --
> migdad
> ------------------------------------------------------------------------
> migdad's Profile: http://www.excelforum.com/member.php...o&userid=33976
> View this thread: http://www.excelforum.com/showthread...hreadid=537502
>
>

[daddylonglegs]

If your start date is in A1 and end date in B1

=NETWORKDAYS(a1+2,b1+2)

NETWORKDAYS requires Analysis ToolPak add-in, an alternative

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))={1,2,3,4,7}))

[Sandy Mann]

"daddylonglegs" <[email protected]>
wrote in message
news:[email protected]...
>
> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))={1,2,3,4,7}))

Nicely thought out


--
Regards

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk

[daddylonglegs]

If your start date is in A1 and end date in B1

=NETWORKDAYS(a1+2,b1+2)

NETWORKDAYS requires Analysis ToolPak add-in, an alternative

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))={1,2,3,4,7}))

....or perhaps a simpler way without using NETWORKDAYS

=SUM(INT((WEEKDAY(A1-{1,2,3,4,7})+B1-A1)/7))....

although if you want to exclude holidays too then I think you need the above SUMPRODUCT formula with an amendment

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1&":"&B1)))={1,2,3,4,7})*(ISNA(MATCH(ROW(INDIRECT(A1&":"&B1)),H1:H10,0))))

where H1:H10 contains a list of holidays

[migdad]

Dear Daddylonglegs
Thanks for your support, but I am still facing problem
I need to know the period days and times between two days to see if the complaints closed within SLA (Service level agreement) or not as I am working in Bank - Customer Service Department
for example, if I would like to count the different days and time between the below two
25/04/2006 10:14 AM and 25/04/2006 11:19 AM
When I use the formula SUM(INT((WEEKDAY(C22-{1,2,3,4,7})+C23-C22)/7))
The results will be one day, but actualy the SLA is less than one day. it should be (0) 1:05 (DD) HH:MM

So please help me on that if you can

[daddylonglegs]

OK, that's quite feasible, I think,but not with the previous formulas I suggested Those assumed you were only looking at full days.

Will your times/dates in C22 and C23 always be within working hours, e.g. Saturday to Wednesday 09:00 to 18:00?

What's the longest period you're likely to have to measure?

What result would you expect where

C22 = 25/04/2006 10:14 AM and
C23 = 26/04/2006 10:00 AM?

[migdad]

OK, that's quite feasible, I think,but not with the previous formulas I suggested Those assumed you were only looking at full days.

Will your times/dates in C22 and C23 always be within working hours, e.g. Saturday to Wednesday 09:00 to 18:00?

What's the longest period you're likely to have to measure?

What result would you expect where

C22 = 25/04/2006 10:14 AM and
C23 = 26/04/2006 10:00 AM?

Thanks,
Actualy I would like to know how many complaints closed within less than 24 hours, from 24 to 48 hours, etc.
Attached below example can explain the required
Complaints # Complaint in Solved in Closing period
1 4/1/06 9:45 4/7/06 9:45
2 4/5/06 8:45 4/14/06 10:45
3 4/5/06 9:45 4/5/06 9:49
4 4/11/06 14:45 4/12/06 9:45
5 4/26/06 9:45 4/29/06 11:45
6 4/27/06 11:11 4/28/06 11:45 This complaints come in weekend and solved in weekend, so the closing period should be 0

Note : The 24 hours meaning for example from today 10:11 AM to tomorrow 10:10 maximum including the working and not working hours.


Tanks again

[daddylonglegs]

OK, assuming start time/date in B1 and end time/date in C1 use this formula

=NETWORKDAYS(B1+2,C1+2)+NETWORKDAYS(C1+2,C1+2)*(MOD(C1,1)-1)-NETWORKDAYS(B1+2,B1+2)*MOD(B1,1)

format as required, e.g. (d) hh:mm. Note this formatting won't show correct results when time period is 32 days or longer....