PDA

View Full Version : request formula



thair younis
04-03-2012, 02:04 AM
Hi
how can i count the days between two date with delete the days off like Friday and Yesterday . by equation .
for example first date in ( A1) and second date in (B1) and the equation in
(c1)

Thank you

Bob Phillips
04-03-2012, 02:15 AM
Try

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))<>6))

thair younis
04-03-2012, 04:26 AM
Thank you very much Xld
but this formula delete just one day not two days
and i wanna delete Friday and Saturday

Bob Phillips
04-03-2012, 04:38 AM
I see no mention of saturday in your original post


=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6))

thair younis
04-03-2012, 04:57 AM
Thanks Xld it worked perfectly
( I see no mention of saturday in your original post)
was my fault sorry
xld if happen unexpected day off for example Thursday by the chance and wanna add it . what should i do for that
And sorry to prolongation .

Bob Phillips
04-03-2012, 05:22 AM
I guess that the best way would be to enter all such dates in a list, name it DaysOff, and use

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))<>6),--(ISERROR(MATCH(ROW(INDIRECT(A1&":"&B1)),DaysOff,0))))

thair younis
04-03-2012, 06:10 AM
thanks a lot Xld
I will try it
thank you

thair younis
04-03-2012, 09:11 PM
Hi Xld
what do you think about this
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6))-1

Bob Phillips
04-04-2012, 01:01 AM
I have no idea. Why are you subtracting 1? Why are you now not including DaysOff? How is this substantially different to what I have given you?

thair younis
04-04-2012, 01:41 AM
Hi Sir Xld
If there day off at a week additional to Friday and Saturday I think we can put subtracting 1 for the result be three days deleted .
sir you think that formula wrong
Thank you

Bob Phillips
04-04-2012, 02:43 AM
As I said, I have no idea. Apart from -1 it is a formula I gave you earlier, so what are you trying to do?