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.

 

[Guest]

NETWORKDAYS()

 

[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

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

Nicely thought out


--
Regards

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) 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 th
complaints closed within SLA (Service level agreement) or not as I a
working in Bank - Customer Service Department
for example, if I would like to count the different days and tim
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 ca

 

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

 

[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....