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

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

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}))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=537502

"daddylonglegs"
wrote in message
news:daddylonglegs.271lrm_1146342302.402@excelforu m-nospam.com...

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

Nicely thought out


--
Regards

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk

daddylonglegs Wrote:
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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=537502

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


--
migdad
------------------------------------------------------------------------
migdad's Profile: http://www.excelforum.com/member.php...o&userid=33976
View this thread: http://www.excelforum.com/showthread...hreadid=537502

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?


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=537502

daddylonglegs Wrote:
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,
Attached Excel file to explain the required


--
migdad
------------------------------------------------------------------------
migdad's Profile: http://www.excelforum.com/member.php...o&userid=33976
View this thread: http://www.excelforum.com/showthread...hreadid=537502

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)*(MO D(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....


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=537502