Forum Discussion

RachelJones's avatar
RachelJones
Qrew Cadet
9 years ago

Formula to calculate number of weekdays last month

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

1 Reply

  • Eric_J_Hansen's avatar
    Eric_J_Hansen
    Qrew Cadet
    9 years ago
    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)