Showing results for 
Search instead for 
Did you mean: 
Regular Visitor


hola amable comunidad


Requiero de su apoyo para poder sumar 5 dias habiles (no contar sabados ni domingos) a una fecha tipo DateTimezone


Tengo un campo Date con miles de fechas, por ejemplo esta fecha (26/05/2023 6:00:00 PM) al sumar los 5 dias habiles mi fecha de salidad debe ser 02/06/2023 6:00:00 PM




Memorable Member
Memorable Member

Your examples are of the DateTime type,  NOT DateTimeZone.  Accordingly, the below code for a Power Query function uses DateTime.  You should be able to adapt it to your actual problem


Also, the code is more generalized and includes an optional Holidays list. You can create the list yourself, which, if you have thousands of dates, would be more efficient, as the code will download an appropriate list each time it executes.


//Note optional Holidays parameter
//You can substitute a self-generated List of holidays, or use the website in the code to download a list
//  If you use the website, be sure to use a recognized country abbreviation
//If you are using holidays, you should separate this list and buffer the result for speed issues.
//  as written, it will download this list every time the function executes

(start as datetime, numDays as number, optional Country as nullable text) =>

    adjNumDays = Number.RoundAwayFromZero(Number.Abs(numDays*7/5))*2,
    yrStart = Date.Year(start),
    yrEnd = Date.Year(Date.AddDays(start,adjNumDays*Number.Sign(numDays))),

//Create list of country specific Holidays
    yrs = {List.Min({yrEnd,yrStart})..List.Max({yrEnd,yrStart})},

    holidays = if Country = null then {} else    
        List.Accumulate(yrs,{},(state,current) =>
                Source=Web.Page(Web.Contents("" & Country & "/" & Number.ToText(current))),
                Data0 = Source{0}[Data],
                DateCol = Table.SelectColumns(Data0,"Date"),
                fullDate = List.Transform(DateCol[Date], each Date.FromText(_ & " " & Number.ToText(current)))
                state & fullDate),

//all Working dates
//Generates a list of working dates in either Ascending or Descending order
//  based on the Sign of numDays
//  starting with the first working date after Start
    allWorkDates = 
                List.DateTimes(start, adjNumDays, #duration(Number.Sign(numDays),0,0,0)),
                    each if _ = start then _ 
                                if Date.DayOfWeek(_)=Day.Saturday 
                                    or Date.DayOfWeek(_)=Day.Sunday 
                                    or List.Contains(holidays, Date.From(_))
                                then null 
                                else _)),
    addWorkDays = if numDays = 0 then start else allWorkDates{Number.Abs(numDays)}
    //List.DateTimes(start, adjNumDays, #duration(Number.Sign(numDays),0,0,0))


Although this is a Power Query forum, if you should want a DAX solution, see direct application of WORKDAY function like in excel 

Helpful resources

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors
Top Kudoed Authors