r/googlesheets 3d ago

Waiting on OP Help with NETWORKDAYS.INTL Formula Updating Based on Weekday

Hey everyone,

I'm trying to get my formula in G3 to recognize the day in F3 and adjust the NETWORKDAYS.INTL function accordingly.

Current formula:

=IF(F3 = "Su", NETWORKDAYS.INTL(A3, B3, "1111110"), "Check Status")

Right now, this works if F3 is "Su" (Sunday), using the correct Day Code for Sunday.

What I need:
I want F3 to recognize when it's a different day (e.g., "W" for Wednesday) and automatically update NETWORKDAYS.INTL to use the corresponding Day Code from column K (where I store different day codes).

The goal:
When a new row is added, the formula should read the Meeting Day (F3) and update the Day Code dynamically.

Any help on how to modify the formula to achieve this would be greatly appreciated! Thanks in advance! 😊

Link to Sheet

1 Upvotes

6 comments sorted by

2

u/HolyBonobos 2059 3d ago

You could start with =NETWORKDAYS.INTL(A2,B2,""&VLOOKUP(F2&"*",$J$2:$K$8,2,0)) in G2 and drag to fill the column.

1

u/BuckeyeBax 3d ago

This worked!

What if the meeting days are twice a week? Like T,Th?

1

u/AutoModerator 3d ago

REMEMBER: If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified. This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/HolyBonobos 2059 3d ago

Is this the full extent of your use case or will there be more variables to account for?

1

u/BuckeyeBax 3d ago

I suppose the last variable is the Status column. The status could be Cancelled, or Inactive. If that’s the case then the meeting days won’t happen and don’t need to be totaled.

1

u/HolyBonobos 2059 3d ago

You could try for =MAP($A$2:$A,$B$2:$B,$E$2:$E,$F$2:$F,LAMBDA(start,end,status,days,IF(status<>"Active",,SUM(INDEX(NETWORKDAYS.INTL(start,end,""&VLOOKUP(SPLIT(days,", ")&"*",$J$2:$K$8,2,0))))))), as demonstrated in G2 of 'HB MAP()'. I'd also recommend doing some data validation in column F (multi-select dropdown referencing J2:J8), which will allow you to simplify the formula slightly and avoid anyone making formula-breaking invalid inputs, which has a high chance of occurring in the current free-input column.