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?

I didn’t understand what Yasin meant LOL But I manage to solve this issue by using template as the excel formula [Screenshot 2024-07-19 at 14.49.07] [Screenshot 2024-07-19 at 14.49.22] [Screenshot 2024-07-19 at 14.49.39]

[Screenshot 2024-07-17 at 09.20.21] Here is the Excel formula that I use [Screenshot 2024-07-17 at 09.20.40] Here is the array of holidays that I want to use

I just re-embedded the video that @Darren_Murphy had in that thread. [image] Date difference excluding weekends and bankholidays Ask for Help @Lise_Delarue - here…

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

[image] EDATE in Excel formula is not working Report a Bug 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.

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 [Screenshot 2024-07-19 at 14.53.26]

[image] Lee: But I manage to solve this issue by using template as the excel formula Yeah I think that was exactly what Yasin did. [image] 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).

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.

Networkdays excel formula

Ask for Help

ThinhDinh July 17, 2024, 1:31pm 3

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

Topic		Replies	Views
Date difference excluding weekends and bankholidays Ask for Help	9	1171	December 28, 2022
Calculation of days excluding weekends Ask for Help	12	2883	February 13, 2024
Trying to add number or subtract number of days from given date, cant Ask for Help	11	216	June 28, 2025
Working Day formula between two date Ask for Help	4	757	December 27, 2022
Hyperformula - WORKDAY.INTL Ask for Help	5	509	November 9, 2021

Networkdays excel formula

Related topics