Forum Discussion
Date / Time difference excluding weekends and factoring working hours
- 1 year ago
so complicated...
(from, to, optional st, optional et) => [s_time = st ?? #time(8, 0, 0), e_time = et ?? #time(17, 0, 0), start = List.Min({from, to}), end = List.Max({from, to}), start_date = Date.From(start), end_date = Date.From(end), gen = List.Generate( () => [c = start_date, sod = List.Max({start, start_date & s_time}), eod = List.Min({end, c & e_time})], (x) => x[c] <= end_date, (x) => [c = Date.AddDays(x[c], 1), sod = c & s_time, eod = List.Min({end, c & e_time})], (x) => if Date.DayOfWeek(x[c], Day.Monday) > 4 then null else x[eod] - x[sod] ), result = if List.NonNullCount({from, to}) < 2 then null else List.Sum(gen)][result]
Hi,
I tried it but it seems I got an issue with start and end because you use #time... instead I could reuse existing field with datetime datatype.
I tried to replace them with #"début" and #"fin" or [début] and [fin] but I got an error with "field not recognized"
Provide sample data as I mentioned above please.
- Heremion1 year agoFrequent Visitor
You'll find a sample below
début dateEC DiffEC dateT dateF 19/09/2022 08:00:00 19/09/2022 14:09:12 0.06:09:12 20/01/2023 17:01:57 19/09/2022 14:09:57 03/09/2024 08:00:00 17/09/2024 08:09:37 3.18:09:37 null 17/09/2024 08:09:09 12/09/2024 08:00:00 12/09/2024 14:09:35 0.06:09:35 null 12/09/2024 16:09:05 06/09/2024 08:00:00 06/09/2024 11:09:53 0.03:09:53 06/09/2024 14:09:45 06/09/2024 14:09:29 19/09/2022 08:00:00 13/10/2022 10:10:09 6.20:10:09 03/08/2023 15:08:12 03/08/2023 15:08:12 06/09/2024 08:00:00 06/09/2024 10:09:45 0.02:09:45 10/09/2024 11:09:31 10/09/2024 11:09:59 19/09/2022 08:00:00 null null 20/09/2022 09:09:18 20/09/2022 09:09:18 19/09/2022 08:00:00 null null null 23/09/2022 09:09:17 09/09/2024 08:00:00 09/09/2024 08:09:09 0.00:09:09 09/09/2024 15:09:37 09/09/2024 15:09:16 19/09/2022 08:00:00 19/09/2022 15:09:19 0.07:09:19 19/09/2022 15:09:11 19/09/2022 15:09:01 09/09/2024 08:00:00 09/09/2024 09:09:37 0.01:09:37 09/09/2024 15:09:14 09/09/2024 15:09:55 null null null 02/09/2024 20:09:54 02/09/2024 20:09:54 19/09/2022 08:00:00 19/09/2022 14:09:44 0.06:09:44 16/02/2023 15:02:04 20/09/2022 10:09:00 09/09/2024 08:00:00 09/09/2024 09:09:42 0.01:09:42 09/09/2024 15:09:44 09/09/2024 15:09:31 09/09/2024 08:00:00 09/09/2024 09:09:05 0.01:09:05 09/09/2024 15:09:56 09/09/2024 15:09:42 20/09/2022 08:00:00 20/09/2022 15:09:30 0.07:09:30 20/09/2022 17:09:34 20/09/2022 17:09:52 21/09/2022 08:00:00 null null 29/11/2022 09:11:03 29/11/2022 09:11:55 21/09/2022 08:00:00 27/09/2022 15:09:54 1.19:09:54 28/09/2022 10:09:01 null 11/09/2024 08:00:00 11/09/2024 16:09:48 0.08:09:48 11/09/2024 17:09:08 11/09/2024 17:09:53 03/09/2024 08:00:00 null null null 03/09/2024 09:09:22 11/09/2024 08:00:00 11/09/2024 16:09:51 0.08:09:51 11/09/2024 17:09:39 11/09/2024 17:09:31 21/09/2022 08:00:00 21/09/2022 15:09:20 0.07:09:20 22/11/2022 16:11:55 29/09/2022 18:09:00 03/09/2024 08:00:00 null null null 03/09/2024 10:09:22 11/09/2024 08:00:00 11/09/2024 17:09:43 0.09:00:00 12/09/2024 10:09:12 12/09/2024 10:09:56 11/09/2024 08:00:00 11/09/2024 17:09:47 0.09:00:00 12/09/2024 10:09:04 12/09/2024 10:09:51 21/09/2022 08:00:00 21/09/2022 17:09:43 0.09:00:00 21/09/2022 17:09:37 21/09/2022 17:09:30 11/09/2024 08:00:00 11/09/2024 17:09:21 0.09:00:00 12/09/2024 11:09:30 12/09/2024 11:09:22 11/09/2024 08:00:00 11/09/2024 17:09:29 0.09:00:00 12/09/2024 11:09:01 12/09/2024 11:09:53 21/09/2022 08:00:00 null null 21/09/2022 18:09:53 21/09/2022 18:09:43 I try to get three fieds :
* diff between début and dateEC
* diff between début and dateT
* diff between début and dateF
excluding weekends
Thx for your help
- dufoq31 year agoCommunity Champion
Difference in days?
- Heremion1 year agoFrequent Visitor
Difference in time. After I can convert in days if I have to.
Examples :
18/09/2024 08:00:00 -> 18/09/2024 10:00:00 = 02:00:00
18/09/2024 08:00:00 -> 19/09/2024 10:00:00 = 11:00:00 according working between 08:00:00 and 17:00:00 each day (so 08:00:00 -> 17:00:00 + 08:00:00 -> 10:00:00)