Forum Discussion
Heremion
1 year agoFrequent Visitor
Date / Time difference excluding weekends and factoring working hours
Hello, After seeing this following post and in order to not reopen it, I tried the function explained at the bottom but it returns sometimes suspicious values. https://community.fabric.microsof...
- 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]
dufoq3
1 year agoCommunity Champion
Hi Heremion, I also replied to that post. Have you tried my soution? If you want help provide sample data in usable format (read note below my post if you don't know how) and expected resuld based on sample data please.
- Heremion1 year agoFrequent Visitor
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"
- dufoq31 year agoCommunity Champion
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