Skip to main content
Question

Formula to calculate number of weekdays last month

  • April 18, 2017
  • 0 replies
  • 60 views
I need to know what month was last month, then calculate the number of weekdays last month. I am creating a summary report of team members' hours by team in the Teams table. So the summary field sums up hours with date during previous month, then another summary field tells me number team members per team. Now I need a 3rd field to tell me how many days were in last month so I can do a utilization % formula that divides last month's logged hours by #team members by 8*last month weekdays.

This has part of the solution, but not the bit about determining which month is last month. https://community.quickbase.com/quickbase/topics/what-is-the-formula-to-return-weekdays-in-a-month
      This topic has been closed for replies.

      • EricJHansen
      • Registered
      • April 18, 2017
      This will get you the date of the first day of last month:

      Adjustmonth(Firstdayofmonth(today()),-1)

      This will get you the month number for last month:

      Month(Adjustmonth(Firstdayofmonth(today()),-1))

      Which would return '3' given today is in April. You can create a formula text field to convert the month number to text, something like:

      Case(month(Adjustmonth(Firstdayofmonth(today()),-1)),
      1,"Jan",
      2,"Feb",
      3,"Mar",
      Etc,
      Etc
      )

      This would give you the last day of last month:

      Adjustmonth(lastdayofmonth(today()),-1)

      This will give you the number of days last month:

      Weekdaysub(
      Adjustmonth(lastdayofmonth(today()),-1),
      Adjustmonth(Firstdayofmonth(today()),-1))
      +1)
      Terms & ConditionsAccessibility statement