tiempos trasncurrido entre dos fechas (solo dias habiles, ni feriados ni sabados ni domingos)

[diegojm]

Hola soy nuevo en Power query, quisiera saber si creando una columna nueva puedo obtener el tiempo transcurrido entre dos fechas pero sin considerar sabados, domingos ni feriados.

que podria poner en una nueva columna ??????  para saber cuales tienes mas de 48 horas

[Anonymous · Accepted solution]

Hi @diegojm ,
Based on your description, I created the following test data and specified 10/2/2024 as a holiday

A	10/1/2024 10:00:00AM	10/4/2024 5:00:00AM
B	10/5/2024 9:00:00AM	10/8/2024 8:00:00PM
C	10/9/2024 10:00:00AM	10/10/2024 10:00:00AM
D	10/11/2024 3:00:00PM	10/14/2024 2:00:00PM

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI00DfUNzIwMlEwNLAyACFHX4iwCUTYFC4aqxOt5ASRM4XIWaLqsICIWkBEAyA6nCFyltgtASJ0cZAmF6gs1GnGcBMhwlCnGSEsigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Start Date", type datetime}, {"End Date", type datetime}}),
    HolidayStart = #datetime(2024, 10, 2, 0, 0, 0),
    HolidayEnd = #datetime(2024, 10, 2, 23, 59, 59),
    GenerateHourlyList = (start as datetime, end as datetime) as list =>
        let
            DurationInHours = Number.RoundDown(Duration.TotalHours(end - start)),
            HourlyList = List.Transform({0..DurationInHours}, each start + #duration(0, _, 0, 0))
        in
            HourlyList,
    AddCustom = Table.AddColumn(
        #"Changed Type", 
        "Total Hours", 
        each let 
            StartDateTime = [#"Start Date"],
            EndDateTime = [#"End Date"],
            HourlyList = GenerateHourlyList(StartDateTime, EndDateTime),
            WorkHours = List.Count(
                List.Select(
                    HourlyList,
                    each 
                        Date.DayOfWeek(_, Day.Monday) < 5 and 
                        (DateTime.From(_) < HolidayStart or DateTime.From(_) > HolidayEnd)
                )
            )
        in 
            WorkHours - 1
    )
in
    AddCustom

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Omid_Motamedise]

use this custom function

let 
    StartDate = [StartDate], 
    EndDate = [EndDate],
    Holidays = {Date.FromText("2024-01-01"), Date.FromText("2024-12-25")}, // Add your holiday dates here
    ListOfDates = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)),
    BusinessDays = List.Select(ListOfDates, each Date.DayOfWeek(_, Day.Sunday) > 1 and not List.Contains(Holidays, _))
in 
    List.Count(BusinessDays)

If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. 

Thank you!

[lbendlin]

Read about https://learn.microsoft.com/en-us/dax/networkdays-function-dax