CODEWITHSUNDEEP
excelexcel-formula
Vera
Vera

Reputation: 37

How to show holidays and Weekdays(weekends which are not holyday included) via excel formula?

Info:My current version of excel is 2016.

Hi currently ive a table that shows the days of the week(mon-sun) based on the day of the week but holydays wasnt included so i tried the next formula(ive also a holyday table which search the HOlydays dates):

=IF(WORKDAY($J$5-1;1;HOLYDAY!$A$2:$A$673)=$J$5;$J$5;"HOL")

Now its also showing the Weekends as Holydays on my table like this:

Mond - Tues - Wedn - Thur - Frid - HOL - HOL - Mond - Tues- HOL(pretend Wedn is a holyday which is correct)and so goes on.

But i was expecting for that result instead:

Mond - Tues - Wedn - Thur - Frid - Satu- Sund - Mond - Tues- HOL

PS:If u need any more info to help me let me know

Upvotes: 0

Views: 126

Answers (1)

ouroboros1
ouroboros1

Reputation: 14459

If all days (including weekend days) need to be converted to "Mond" etc., except when they are included in a list of self-declared "Holydays" (holidays), then you don't need WORKDAY() (or NETWORKDAYS() for that matter). You can just check if a cell has a match in your holiday range. If so, return "HOL", else return "Mond" etc.

Not exactly sure how to imagine your table, but something like the following should work:

=IFERROR(IF(MATCH(J5;HOLYDAY!$A$2:$A$673;0);"HOL");LEFT(TEXT(J5;"DDDD");4))

Upvotes: 1

Related Questions