Calculation of days excluding weekends

Ask for Help
1

Hello,

Is it possible to calculate the number of days between two days without counting weekends?

I know you can calculate with “date difference” but I can’t find a way to subtract the weekend.

Example:
The number of days (or minutes) between 05/02/2024 at 05:00 and 22/02/2024 at 05:00.

Thank you

2

From AI:

Sure you can use the Excel formula:

=NETWORKDAYS(A1, A2, A4:A10)

Assuming you have the start date in cell A1 and the end date in cell A2, and a list of holidays in cells A4 to A10.

This formula calculates the number of business days between the dates in A1 and A2, excluding weekends and the holidays listed from A4 to A10.

3

@tuzin has a Math column that does this that will make your head explode. See below:

4 Likes
4

How can i use this ?
In Java script code ?
What values do i need to change ?

5

It would be used in a Math column.

However, I’ve just realised that formula doesn’t quite do exactly what you are looking for. It adds a given number of business days to a start date, whereas you want to calculate the number between two dates. So it would need to be modified somewhat.

@tuzin is always up for a challenge. He might do that if you ask him nicely :wink:

6

I’m 100% willing to try this a bit later. @FlorianA Please remind me about it if I don’t post in the next 24h :sweat_smile:

1 Like
7

Hi Tuzin,

Can you really help me ? Or it is a joke ?

8

That would be a bad joke. I’m really eager to help

9

Oh great!
I’m new to this forum
I’ll explain what I need
I need to put a start date in a “date entry” and an end date in another “date entry”, and I need to have a list of all the working days in a sort of “join list”.

Do you understand? And will you be able to help me?

10

image
image453×870 61.3 KB

My setup, tested in Safari, which has been problematic with Date functions in JavaScript in the past.

image

Date Start & End, user-specific columns, Date Pickers write into these columns.

Timezone: gotta have this to use in the JS function, since Glide seems to return UTC-based values in the JS function instead of returning it as is. Say I choose 15th Feb, it returns a Zulu timestamp of 5PM 14th Feb (I’m UTC+7).

image

Working Days JS: JavaScript column.

const timezoneOffset = parseInt(p3, 10);
const start = new Date(p1);
const end = new Date(p2);

start.setHours(start.getHours() + timezoneOffset);
end.setHours(end.getHours() + timezoneOffset, 59, 59, 999);

let currentDate = new Date(start);
const workingDays = [];

while (currentDate <= end) {
    if (currentDate.getDay() >= 1 && currentDate.getDay() <= 5) {
 workingDays.push(currentDate.toISOString().split('T')[0]);
    }
    currentDate.setDate(currentDate.getDate() + 1);
}

return workingDays.join(', ');

p1: Start
p2: End
p3: Timezone

2 Likes
11

Thank you very much for this answer
I will test this as soon as possible

12

JavaScript column seems to be the only option to return a joined list of working days. I’d stick to the solution provided by @ThinhDinh

13

It can also be done with a helper table, but that certainly isn’t a single column solution, and the maximum date range would be limited to the number of rows in the helper table.

2 Likes