Networkdays excel formula

[Lee]

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?

[Lee]

Here is the Excel formula that I use

Here is the array of holidays that I want to use

[ThinhDinh]

I just re-embedded the video that @Darren_Murphy had in that thread.

Date difference excluding weekends and bankholidays

@Lise_Delarue - here…

[Lee]

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

[ThinhDinh]

EDATE in Excel formula is not working

So I managed to run formulas in excel by creating a column template and replacing the values in it, mostly the issue was because the I can’t add Double Quotations in the Excel formula like the image below [image] But the workaround is to prepare the excel formula in a template column to ensure that all the text is exactly brought from the backend properly to send it to the formula column as below [image] Then map it to the excel formula column will work eventually [image]

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

[Lee]

I didn’t understand what Yasin meant LOL

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

[Lee]

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

[ThinhDinh]

@Lee

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

Yeah I think that was exactly what Yasin did.

@Lee

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

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

[Darren_Murphy]

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.