none
Adding a set amount of working days to a column of dates RRS feed

  • Question

  • Hi,

    I currently have a column (COMPLETEDDATE) on my table which is all just dates. I'm now trying to create another column in the same table which is all the dates from the COMPLETEDDATE column +5 working days. Can anyone help me try and do this on power query?

    Tuesday, May 1, 2018 9:55 AM

Answers

  • I think that the best option here is to use a custom function in Power Query that  emulates the WORKDAY.INTL function in Excel. This way, the workday calculation logic is separated from your main code, and you can reuse the custom function in any future query, as if it were a built-in function.

    You can paste the following code into a blank query and name it Date_Workday.

    (startDate as date, days, optional weekendDayMask as nullable text, optional holidays as nullable list) =>
    let
        AbsDays = Number.Abs(days), //to handle negative days
        IsValidWeekendDayMask = if weekendDayMask = null then true 
                                else Text.Length(weekendDayMask) = 7 and weekendDayMask <> "1111111" and List.ContainsAll({"1", "0"}, Text.ToList(weekendDayMask)), 
        WeekDayNames = {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"},
        WeekendDays = if weekendDayMask = null then {"Saturday", "Sunday"} 
                      else List.Transform(Text.PositionOf(weekendDayMask, "1", Occurrence.All), each WeekDayNames{_}),
        AddedDays = if List.Count(WeekendDays) = 0 then //numbers selected are arbitrary but designed to ensure range of dates in DateList are sufficient and not too large
                       Number.RoundUp(AbsDays * 1.2) 
                    else AbsDays + Number.RoundUp(AbsDays*List.Count(WeekendDays)),
        DateList = if days >= 0 then List.Buffer(List.Dates(startDate, AddedDays, #duration(1, 0, 0, 0)))
                   else List.Buffer(List.Dates(startDate, AddedDays, - #duration(1, 0, 0, 0))),
        RemovedWeekendDates = List.Select(DateList, each not List.Contains(WeekendDays, Date.DayOfWeekName(_))),
        Holidays = if holidays = null then {}
                   else if days >= 0 then List.Select(holidays, each _ <= List.Last(RemovedWeekendDates) and _ >= List.First(RemovedWeekendDates))
                   else List.Select(holidays, each _ >= List.Last(RemovedWeekendDates) and _ <= List.First(RemovedWeekendDates)),
        RemovedHolidays = List.Difference(RemovedWeekendDates, Holidays),
        StartDateOffset = if RemovedHolidays{0} = startDate then List.Skip(RemovedHolidays) else RemovedHolidays,
        WorkdayDate = if IsValidWeekendDayMask then 
                          if AbsDays = 0 then startDate else StartDateOffset{AbsDays - 1}
                      else error "You have entered an invalid weekend day mask"
    in
        WorkdayDate

    Now that's a lot of code. The good news is that you don't have to care about how it works, just like you don't care about how a built-in function is constructed internally.

    You can use the function in your main table like:

    Date_Workday([COMPLETEDDATE], 5)

    By default, the weekend is Saturday and Sunday. If you wish, you can add a weekend mask, just like you can do in Excel:

    Date_Workday([COMPLETEDDATE], 5, "0000110")

    In this case, the weekend days are set to Friday and Saturday.

    If you need to exclude holidays in the calculation, you include a list of holiday dates:

    Date_Workday([COMPLETEDDATE], 5, null, {#date(2018,1,1), #date(2018, 3, 5), ...})

    In this case, we're using the default weekend days, and supplied a list of holiday dates. The holiday list should come from a table column, or a variable with a list of dates (like HolidayList = {#date(2018,1,1), #date(2018, 3, 5), ...}


    • Edited by Colin Banfield Tuesday, May 1, 2018 6:32 PM
    • Marked as answer by SOKane07 Thursday, May 3, 2018 1:38 PM
    Tuesday, May 1, 2018 6:31 PM

All replies

  • Just adding 5 calendar days would be custom column like this:

    [COMPLETEDDATE]+#duration(5,0,0,0))

    How do you define the working days? Do you just exclude the weekends, or do you have a table with bank holidays specific to your country?

    Tuesday, May 1, 2018 10:11 AM
  • I don't currently have working days defined as my only experience prior to this with using working days is the WORKDAYS function on excel. So I've been having trouble figuring out how to include the workdays in the calculation. Weekends and bank holidays would have to be excluded but making a new table isn't an option for me, as silly as that might sound.
    Tuesday, May 1, 2018 10:58 AM
  • Weekends are gonna be solved by this:
    [AssignedDate]+#duration(5,0,0,0) + (
    if Date.DayOfWeek([AssignedDate]+#duration(5,0,0,0)) = 6 
    then #duration(2,0,0,0) else 
    if Date.DayOfWeek([AssignedDate]+#duration(5,0,0,0)) = 0 
    then #duration(1,0,0,0) 
    else #duration(0,0,0,0))

    but for bank holidays you would need a way to specify which dates to exclude - easiest to do with a separate table (can be intermediate step in the current query)
    Tuesday, May 1, 2018 11:06 AM
  • I think that the best option here is to use a custom function in Power Query that  emulates the WORKDAY.INTL function in Excel. This way, the workday calculation logic is separated from your main code, and you can reuse the custom function in any future query, as if it were a built-in function.

    You can paste the following code into a blank query and name it Date_Workday.

    (startDate as date, days, optional weekendDayMask as nullable text, optional holidays as nullable list) =>
    let
        AbsDays = Number.Abs(days), //to handle negative days
        IsValidWeekendDayMask = if weekendDayMask = null then true 
                                else Text.Length(weekendDayMask) = 7 and weekendDayMask <> "1111111" and List.ContainsAll({"1", "0"}, Text.ToList(weekendDayMask)), 
        WeekDayNames = {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"},
        WeekendDays = if weekendDayMask = null then {"Saturday", "Sunday"} 
                      else List.Transform(Text.PositionOf(weekendDayMask, "1", Occurrence.All), each WeekDayNames{_}),
        AddedDays = if List.Count(WeekendDays) = 0 then //numbers selected are arbitrary but designed to ensure range of dates in DateList are sufficient and not too large
                       Number.RoundUp(AbsDays * 1.2) 
                    else AbsDays + Number.RoundUp(AbsDays*List.Count(WeekendDays)),
        DateList = if days >= 0 then List.Buffer(List.Dates(startDate, AddedDays, #duration(1, 0, 0, 0)))
                   else List.Buffer(List.Dates(startDate, AddedDays, - #duration(1, 0, 0, 0))),
        RemovedWeekendDates = List.Select(DateList, each not List.Contains(WeekendDays, Date.DayOfWeekName(_))),
        Holidays = if holidays = null then {}
                   else if days >= 0 then List.Select(holidays, each _ <= List.Last(RemovedWeekendDates) and _ >= List.First(RemovedWeekendDates))
                   else List.Select(holidays, each _ >= List.Last(RemovedWeekendDates) and _ <= List.First(RemovedWeekendDates)),
        RemovedHolidays = List.Difference(RemovedWeekendDates, Holidays),
        StartDateOffset = if RemovedHolidays{0} = startDate then List.Skip(RemovedHolidays) else RemovedHolidays,
        WorkdayDate = if IsValidWeekendDayMask then 
                          if AbsDays = 0 then startDate else StartDateOffset{AbsDays - 1}
                      else error "You have entered an invalid weekend day mask"
    in
        WorkdayDate

    Now that's a lot of code. The good news is that you don't have to care about how it works, just like you don't care about how a built-in function is constructed internally.

    You can use the function in your main table like:

    Date_Workday([COMPLETEDDATE], 5)

    By default, the weekend is Saturday and Sunday. If you wish, you can add a weekend mask, just like you can do in Excel:

    Date_Workday([COMPLETEDDATE], 5, "0000110")

    In this case, the weekend days are set to Friday and Saturday.

    If you need to exclude holidays in the calculation, you include a list of holiday dates:

    Date_Workday([COMPLETEDDATE], 5, null, {#date(2018,1,1), #date(2018, 3, 5), ...})

    In this case, we're using the default weekend days, and supplied a list of holiday dates. The holiday list should come from a table column, or a variable with a list of dates (like HolidayList = {#date(2018,1,1), #date(2018, 3, 5), ...}


    • Edited by Colin Banfield Tuesday, May 1, 2018 6:32 PM
    • Marked as answer by SOKane07 Thursday, May 3, 2018 1:38 PM
    Tuesday, May 1, 2018 6:31 PM
  • This was exactly what I needed. Thanks.
    Thursday, May 3, 2018 1:38 PM
  • Great solution. Tried it in PowerBI. Just be careful with your local language.

    I.e. Date.DayOfWeekName() may return "Dienstag" instead of "Tuesday" in German language. Then, the whole code will provide days instead of workdays and will be wrong

    Friday, October 26, 2018 1:24 PM
  • thanks for this,

    I have been using this code but I noticed that when I try to add 1 working day to any Friday, the resulting column just says "Error" 

    Any ideas why?

    Help much appreciated

    Thursday, November 8, 2018 4:23 PM
  • Great solution. Tried it in PowerBI. Just be careful with your local language.

    I.e. Date.DayOfWeekName() may return "Dienstag" instead of "Tuesday" in German language. Then, the whole code will provide days instead of workdays and will be wrong

    I actually updated the function for international use after my last post:

    (startDate as date, days as number, optional weekendDayMask as nullable text, optional holidays as nullable list, optional culture as nullable text) =>
    let
        AbsDays = Number.Abs(days), //to handle negative days
        IsValidWeekendDayMask = if weekendDayMask = null then true 
                                else Text.Length(weekendDayMask) = 7 and weekendDayMask <> "1111111" and List.ContainsAll({"1", "0"}, Text.ToList(weekendDayMask)), 
        WeekDayNames = List.Buffer(List.Transform({2..8}, each Text.Proper(Date.DayOfWeekName(Date.From(_, culture), culture)))), 
        WeekendDays = if weekendDayMask = null then {WeekDayNames{5}, WeekDayNames{6}} 
                         else List.Transform(Text.PositionOf(weekendDayMask, "1", Occurrence.All), each WeekDayNames{_}),
        AddedDays = if List.Count(WeekendDays) = 0 then //numbers selected are arbitrary but designed to ensure range of dates in DateList are sufficient and not too large
                       Number.RoundUp(AbsDays * 1.2) 
                    else AbsDays + AbsDays * List.Count(WeekendDays),
        DateList = if days >= 0 then List.Buffer(List.Dates(startDate, AddedDays, #duration(1, 0, 0, 0)))
                   else List.Buffer(List.Dates(startDate, AddedDays, - #duration(1, 0, 0, 0))),
        RemovedWeekendDates = List.Select(DateList, each not List.Contains(WeekendDays, Date.DayOfWeekName(_))),
        Holidays = if holidays = null then {}
                   else if days >= 0 then List.Select(holidays, each _ <= List.Last(RemovedWeekendDates) and _ >= List.First(RemovedWeekendDates))
                   else List.Select(holidays, each _ >= List.Last(RemovedWeekendDates) and _ <= List.First(RemovedWeekendDates)),
        RemovedHolidays = List.Difference(RemovedWeekendDates, Holidays),
        StartDateOffset = if RemovedHolidays{0} = startDate then List.Skip(RemovedHolidays) else RemovedHolidays,
        WorkdayDate = if IsValidWeekendDayMask then 
                          if AbsDays = 0 then startDate else StartDateOffset{AbsDays - 1}
                      else error "You have entered an invalid weekend day mask"
    in
        WorkdayDate

    Thursday, November 8, 2018 5:40 PM
  • thanks for this,

    I have been using this code but I noticed that when I try to add 1 working day to any Friday, the resulting column just says "Error" 

    Any ideas why?

    Help much appreciated

    How are you adding this additional day? Can you provide your code?
    Thursday, November 8, 2018 5:42 PM
  • I am using the exact code you provided,

    Just 1 for the days parameter and any Friday for the start date parameter, with nothing for the holidays and nothing for the weekend day mask.

    It throws an error  "an error occured in the query.expression.error: there werent enough elements in the enumeration to complete the operation" details: list

    It seems to work if I add 2 or 3 days on to a Friday though which is strange

    thanks for the quick response

    Thursday, November 8, 2018 6:09 PM
  • there is a fault with the code, the only day is removed from the list when the start day is a Friday and the days = 1
    Thursday, November 8, 2018 8:16 PM
  • There are a couple of boundary conditions that cause the function to return the incorrect result. Nice catch!

    Please try the following updated code:

    (startDate as date, days as number, optional weekendDayMask as nullable text, optional holidays as nullable list, optional culture as nullable text) =>
    let
        AbsDays = Number.Abs(days), //to handle negative days
        IsValidWeekendDayMask = if weekendDayMask = null then true
                                else Text.Length(weekendDayMask) = 7 and weekendDayMask <> "1111111" and List.ContainsAll({"1", "0"}, Text.ToList(weekendDayMask)),
        WeekDayNames = List.Buffer(List.Transform({2..8}, each Text.Proper(Date.DayOfWeekName(Date.From(_, culture), culture)))),
        WeekendDays = if weekendDayMask = null then {WeekDayNames{5}, WeekDayNames{6}}
                         else List.Transform(Text.PositionOf(weekendDayMask, "1", Occurrence.All), each WeekDayNames{_}),
        AddedDays = if List.Count(WeekendDays) = 0 then //numbers selected are arbitrary but designed to ensure range of dates in DateList are sufficient and not too large
                       Number.RoundUp(AbsDays * 1.2)
                    else AbsDays + AbsDays * List.Count(WeekendDays) + List.Count(holidays) + 1,
        DateList = if days >= 0 then List.Buffer(List.Dates(Date.AddDays(startDate, 1), AddedDays, #duration(1, 0, 0, 0)))
                   else List.Buffer(List.Dates(Date.AddDays(startDate, -1), AddedDays, - #duration(1, 0, 0, 0))),
        RemovedWeekendDates = List.Select(DateList, each not List.Contains(WeekendDays, Date.DayOfWeekName(_))),
        Holidays = if holidays = null then {}
                   else if days >= 0 then List.Select(holidays, each _ <= List.Last(RemovedWeekendDates) and _ >= List.First(RemovedWeekendDates))
                   else List.Select(holidays, each _ >= List.Last(RemovedWeekendDates) and _ <= List.First(RemovedWeekendDates)),
        RemovedHolidays = List.Difference(RemovedWeekendDates, Holidays),
        WorkdayDate = if IsValidWeekendDayMask then
                          if AbsDays = 0 then startDate else RemovedHolidays{AbsDays - 1}
                      else error "You have entered an invalid weekend day mask"
    in
        WorkdayDate

     

    Thursday, November 8, 2018 10:42 PM
  • Thanks mate, we ended up fixing ourselves by having an if Friday and days =1 but I will also test this code above

    thanks

    Friday, November 9, 2018 10:23 AM
  • Just tried the new code, without looking into it in detail - no time atm, I don't think it works.

    It throws out errors for everyline I think now

    Friday, November 9, 2018 10:44 AM
  • Not going well for me, is it? :) Fixed one problem to create another. Sigh! Let's try this again...

    (startDate as date, days as number, optional weekendDayMask as nullable text, optional holidays as nullable list, optional culture as nullable text) =>
    let
        AbsDays = Number.Abs(days), //to handle negative days
        IsValidWeekendDayMask = if weekendDayMask = null then true 
                                else Text.Length(weekendDayMask) = 7 and weekendDayMask <> "1111111" and List.ContainsAll({"1", "0"}, Text.ToList(weekendDayMask)), 
        WeekDayNames = List.Buffer(List.Transform({2..8}, each Text.Proper(Date.DayOfWeekName(Date.From(_, culture), culture)))), 
        WeekendDays = if weekendDayMask = null then {WeekDayNames{5}, WeekDayNames{6}} 
                         else List.Transform(Text.PositionOf(weekendDayMask, "1", Occurrence.All), each WeekDayNames{_}),
        AddedDays = if List.Count(WeekendDays) = 0 then //numbers selected are arbitrary but designed to ensure range of dates in DateList are sufficient and not too large
                       Number.RoundUp(AbsDays * 1.2) 
                    else AbsDays + AbsDays * (try List.Count(WeekendDays) otherwise 0) + (try List.Count(holidays) otherwise 0) + 1,
        DateList = if days >= 0 then List.Buffer(List.Dates(Date.AddDays(startDate, 1), AddedDays, #duration(1, 0, 0, 0)))
                   else List.Buffer(List.Dates(Date.AddDays(startDate, -1), AddedDays, - #duration(1, 0, 0, 0))),
        RemovedWeekendDates = List.Select(DateList, each not List.Contains(WeekendDays, Date.DayOfWeekName(_))),
        Holidays = if holidays = null then {}
                   else if days >= 0 then List.Select(holidays, each _ <= List.Last(RemovedWeekendDates) and _ >= List.First(RemovedWeekendDates))
                   else List.Select(holidays, each _ >= List.Last(RemovedWeekendDates) and _ <= List.First(RemovedWeekendDates)),
        RemovedHolidays = List.Difference(RemovedWeekendDates, Holidays),
        WorkdayDate = if IsValidWeekendDayMask then 
                          if AbsDays = 0 then startDate else RemovedHolidays{AbsDays - 1}
                      else error "You have entered an invalid weekend day mask"
    in
        WorkdayDate

    Friday, November 9, 2018 1:34 PM
  • Thanks mate, we ended up fixing ourselves by having an if Friday and days =1 but I will also test this code above

    thanks

    Hello, SJP00000!

    How do you fixed the issue? I'm facing the same situation.

    Wednesday, December 19, 2018 1:01 PM
  • Thanks mate, we ended up fixing ourselves by having an if Friday and days =1 but I will also test this code above

    thanks

    Hello, SJP00000!

    How do you fixed the issue? I'm facing the same situation.

    Hi bgeds,

    Please let me know the problem that you encountered when using the last script that I posted. It should also fix other edge condition that you might encounter in the future, and SJP00000 would not be unaware of.

    Wednesday, December 19, 2018 1:58 PM
  • Hi Colin,

    Thanks for your quickly response, I i'm facing a trouble with the time to check every line using the try function. I want to know how SJP00000 did to run the code and compare the time to run.

    My goal is get the previous workday of  a set of cash flows to adjust the balance of the day.

    Regards,

    bgeds

    Wednesday, December 19, 2018 3:15 PM
  • I i'm facing a trouble with the time to check every line using the try function

    I don't follow. Using the posted code with the function name 'Date_Workday', the previous workday expression from a specific date would be Date_Workday([date column], -1, "0000011"), or as a new column in your table:

    PreviousWorkday = Table.AddColumn(PreviousStepName, "Previous Workday", each Date_Workday([DateColumnName], -1, "0000011"))

    If this not what you're after, perhaps you can provide a sample table of how you want the end result to look.

    Wednesday, December 19, 2018 4:05 PM
  • I've successfully entered your code into a blank query, and it recognizes it as a function, but I can't seem to call out the function in my main table. Can you please advise to what I'm doing wrong?
    Monday, March 23, 2020 8:52 PM