Formula to calculate number of weekdays last month

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • In Progress
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
Photo of Rachel

Rachel

  • 192 Points 100 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
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)