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?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.