none
get working day month current RRS feed

Answers

  • Hi Germán,

    Here's an M function that, given a date, returns the number of business days (i.e. weekdays) in the month the date falls in.

    = (date as date) =>
    let
        month = List.Generate(() => Date.StartOfMonth(date), each _ <= Date.EndOfMonth(date), each Date.AddDays(_, 1)),
        daysOfWeek = List.Transform(month, each Date.DayOfWeek(_)),
        weekdays = List.Select(daysOfWeek, each _ <> 0 and _ <> 6)
    in
        List.Count(weekdays)

    Note that the above assumes Sunday is 0 and Saturday is 6. However, the M function Date.DayOfWeek is locale-sensitive, so in some cases the above code would need to be tweaked.

    If you'd like to filter a table in order to only see weekdays, you can select the date column, click Add Column->Date->Day->Day of Week, and then filter the DayOfWeek column to exclude Saturday and Sunday.

    Ehren


    Monday, November 9, 2015 11:06 PM
    Owner

All replies

  • Hi Germán,

    What specifically are you looking for?  Is it the number of business days in a month? (Like the Days in Month function) Are you trying to filter on whether a day is a business day or not? Or do you want a list of all the business days in a given month?

    Cheers,
    Anton

    Monday, November 9, 2015 11:00 PM
  • Hi Germán,

    Here's an M function that, given a date, returns the number of business days (i.e. weekdays) in the month the date falls in.

    = (date as date) =>
    let
        month = List.Generate(() => Date.StartOfMonth(date), each _ <= Date.EndOfMonth(date), each Date.AddDays(_, 1)),
        daysOfWeek = List.Transform(month, each Date.DayOfWeek(_)),
        weekdays = List.Select(daysOfWeek, each _ <> 0 and _ <> 6)
    in
        List.Count(weekdays)

    Note that the above assumes Sunday is 0 and Saturday is 6. However, the M function Date.DayOfWeek is locale-sensitive, so in some cases the above code would need to be tweaked.

    If you'd like to filter a table in order to only see weekdays, you can select the date column, click Add Column->Date->Day->Day of Week, and then filter the DayOfWeek column to exclude Saturday and Sunday.

    Ehren


    Monday, November 9, 2015 11:06 PM
    Owner
  • Hello,

    here is code for a list of workdays in current month (thanks to Ehren):

    let
        Today = DateTime.Date(DateTime.LocalNow()),
        Month = List.Generate(() => Date.StartOfMonth(Today), each _ <= Date.EndOfMonth(Today), each Date.AddDays(_, 1)),
        WorkDays = List.Select(Month, each Date.DayOfWeek(_)<5)
    in
        WorkDays

    In my locale Sunday is 6 and Saturday is 5, so you have to check your locale when applying last step.

    But if you need to exclude national holidays, it is better to search the web for national workdays calendar (may be on some HR sites)

    Good Luck


    Maxim Zelensky Excel Inside

    Tuesday, November 10, 2015 9:00 AM