date not include sunday,saturday and not include red date in tabel

[alamsyah]

I Want formula with last date every month but not include sunday and saturday and not include date in col C (red date)
this is my formula but while change date in col C (red date) ,result not changed

Please Login or Register  to view this content.

wawa.png

[Bo_Ry]

Please try at D5
=WORKDAY(D5+1,-1,$C$5:$C$15)

[alamsyah]

Bo_ry i want formula in col D without helper column thank
find last date and not include sunday ,saturday and not include red date
that is imposibel

[AliGW]

Welcome to the forum.

What should happen if the end of the month is a weekend or holiday?

[AliGW]

In D4 copied down:

=WORKDAY(EOMONTH(DATE(2019,1,1),ROWS(D$5:D5)-1)+1,-1,$C$5:$C$15)

Excel 2016 (Windows) 32 bit

	C	D	E
3		RESULT USING FORMULA
4	RED DATE	LAST DATE	CHEK DATE
5	31/01/2019	30/01/2019	Wednesday
6	05/02/2019	28/02/2019	Thursday
7	07/03/2019	29/03/2019	Friday
8	03/04/2019	30/04/2019	Tuesday
9	19/04/2019	31/05/2019	Friday
10	01/05/2019	28/06/2019	Friday
11	25/05/2019	31/07/2019	Wednesday
12	01/06/2019	30/08/2019	Friday
13	05/06/2019	30/09/2019	Monday
14	06/06/2019	31/10/2019	Thursday
15	25/12/2019	29/11/2019	Friday
16		31/12/2019	Tuesday

Sheet: Sheet1

[alamsyah]

thank you AliGW example last date in month(12) = 31/12/2019
first : chek if 31/12/2019 not match (not include) with red date (col c)
second : if 31/12/2019 not match with red date and 31/12/2019 not sunday or saturday then result is 31/12/2019
but if =31/12/2019 equal to red date(col (c)) then last date become 31-12-2019 = 30/12/2019 and then chek again to second step not sunday or saturday
30/12/2019= Saturday then 30-12-2019 become 29-12-2019
or if 30/12/2019 is sunday then reduced 30-12-2019 - 2 (day) = 28-12-2019
if 28-12-2019 not sunday and not saturday and not include in red date then 28-12-2019 is last date in month(12)

[Bo_Ry]

Please forgive me.
Could you please kindly try again at D5

=WORKDAY(EOMONTH("1/1/19",ROWS(D$5:D5)-1)+1,-1,$C$5:$C$15)

[AliGW]

Did you try my suggestion? In what way does it NOT do what you have requested?

[alamsyah]

oh thankyou Bo_ry is great and Ferpect
can you explain why is -1
=WORKDAY(EOMONTH("1/1/19",ROWS(D$5:D5)-1)+1,-1,$C$5:$C$15)

[AliGW]

Did you even look at post #5???

By adding a day and then looking at the day before that (+1,-1,) you are coercing Excel to judge whether the date in D4 fits the criteria. One balances out the other. If you try to do it with a 0, Excel fails to work it out correctly because there is no date range.

[alamsyah]

i am sorri Aligw ,i am has tes and perfecto
thankyou can you explain me
=WORKDAY(EOMONTH(DATE(2019,1,1),ROWS(D$5:D5)-1)+1,-1,$C$5:$C$15)
what mean -1

[AliGW]

See post #10.

[alamsyah]

ok thankyou aliWg and Bo_Ry case is solved

[AliGW]

Fab - thanks for letting us know.