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

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

CodePudding user response：

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))