# Networkdays excel formula

Hi,

I am trying to calculate date difference from StartDate to EndDate, and I found interesting excel command from @gvalero : Date difference excluding weekends and bankholidays

When I test it, I can use:
NETWORKDAYS(A1, A2, {“10/2/2024”, “11/1/2024”, “11/12/2024”, “11/25/2024”, “12/24/2024”, “12/25/2024”, “12/26/2024”})

But I cannot change it to NETWORKDAYS(A1, A2, {A3})
I put A3 = “10/2/2024”, “11/1/2024”, “11/12/2024”, “11/25/2024”, “12/24/2024”, “12/25/2024”, “12/26/2024”

or NETWORKDAYS(A1, A2, A3)
I put A3 = {“10/2/2024”, “11/1/2024”, “11/12/2024”, “11/25/2024”, “12/24/2024”, “12/25/2024”, “12/26/2024”}

I also need to be able to set the A3 to “SpecialHolidays”

is there I can do to fix this issue?

Here is the Excel formula that I use

Here is the array of holidays that I want to use

2 Likes

Thank you @ThinhDinh for the info

the solution needs to create a whole start & end date range record/table … which in the end can calculate the leave period… but in my case, I need to do that for all leave submissions that the employer submitted, so I don’t think it’s possible/scalable for my case

I think NetworksDay is the solution, it’s just that I don’t want to “hardcode” the special holidays array data

Can you try this workaround? It’s not the same function, but your root problem might be due to the double quotes.

1 Like

I didn’t understand what Yasin meant LOL

But I manage to solve this issue by using template as the excel formula

one more thing … if someone still has some issues, you may want to check your start & end date format, since if you use the default Glide date format it will use mm/dd/yyyy, hh:mm AM/PM, while networkdays I think only need mm/dd/yyyy (without the time), so I use split & single first

Yeah I think that was exactly what Yasin did.

For a more consistent formatting, you can try using columns to extract the date, month and year, then join them together using a template column, or the “Format Date” column (but it has shown problems on iOS before).

1 Like

Be careful with that. Date formatting is device specific. What you see in the Glide Builder is just the way that your specific device represents the date and time. Other devices will format the date in different ways. For example, some may use MM/DD/YYYY, and others may use DD/MM/YYYY. Some may use a comma to separate the date and time, and others may not. You may find that your split may not work correctly on some devices.

When dealing with dates in this way, it is always best to first convert them to integers using a Math column. This ensures the correct results on all devices.

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.