Creating table with month days and checking if workdays

User avatar
ErikJan
5StarLounger
Posts: 1062
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Creating table with month days and checking if workdays

Post by ErikJan »

I'm creating a small table in Excel where I have the Months on top (Jan > Dec, month numbers 1 through 12) and the days in rows.
I've pulled all the rows down to 31 for the days.

What I like to do is highlight the work days in this year-table (adjusting for holidays).

I found a formula to check if a date is a workday: =WORKDAY(date-1,1,holidays)=date

And that works. However in my table I -obviously- have non-existing dates in my setup (like e.g. June 31). As I build the date for the formula above using the "Date"-function (like: Date(Year, Month, Day)), when I enter resp. 2022, 06, 31, Excel is clever enough to return July 1st.
Now that's nice, but it defeats the formula above and now flags this date as a working day...

Of course I can eliminate June 31st (and other non-existing dates) from my table manually, but I'd like Excel to automatically flag non existing dates as non-workdays as well (think e.g. leap years and Feb 29th).

Any suggestions how I can do this, i.e. how do I find out that a date is not possible?

User avatar
HansV
Administrator
Posts: 73825
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Creating table with month days and checking if workdays

Post by HansV »

Let's say you have year in K1, month number in L1 and day number in M1.The formula
=MONTH(DATE(K1,L1,M1))=L1
returns TRUE if the date in K1,L1,M1 is a "real" date and FALSE otherwise.
So if K1=2022, L1=2 and M1=29, the formula returns FALSE, but if K1=2024, L1=2 and M1=29, the formula will return TRUE.
Can you adapt this for your purpose?
Regards,
Hans

User avatar
ErikJan
5StarLounger
Posts: 1062
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Creating table with month days and checking if workdays

Post by ErikJan »

I think so... Nice approach, thanks!

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 13108
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Creating table with month days and checking if workdays

Post by ChrisGreaves »

HansV wrote:
05 Jan 2022, 11:17
returns TRUE if the date in K1,L1,M1 is a "real" date and FALSE otherwise.
:clapping: :clapping: :clapping: :chocciebar: :cheers:
Don’t let a good crisis go to waste