Quickbase formula for Last WorkDay of a month

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered
Photo of Scott

Scott

  • 258 Points 250 badge 2x thumb

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
This seems to work. The field [test last day of month - date] is any date.


WeekdayAdd(LastDayOfMonth([test last day of month - date]),0)

Photo of Scott

Scott

  • 258 Points 250 badge 2x thumb
Thanks so much for responding but If the last day of the month is a weekend day it goes to the NEXT weekday - which is in the following month.  I need it go go back to the last workday - or the previous friday.
Photo of Scott

Scott

  • 258 Points 250 badge 2x thumb
I tried this and it seemed to work!  WeekdayAdd(LastDayOfMonth([For Month Beginning]),-1)

Thanks so much Mark!  Could not have gotten it without your help!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
You should test a few  dates, I think that subtracting 1 will give you a Thursday if the last date of the month was a Friday.  That is why i subtracted zero days.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
Oh, now I just read your previous comment.  I think the final formula still needs some work to get it to work on all cases.
Photo of Scott

Scott

  • 258 Points 250 badge 2x thumb
You are right.  Sigh....
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
Try this, it subtracts a day only if it has to - ire the last day of the month is on a weekend.

var number DayOfWeekLastdayOfMonth= DayOfWeek([test last day of month - date]);

var number DaysToAdjust = If($DayOfWeekLastdayOfMonth=6 or $DayOfWeekLastdayOfMonth=0,-1,0);

WeekdayAdd(LastDayOfMonth([test last day of month - date]),$DaysToAdjust)
Photo of Scott

Scott

  • 258 Points 250 badge 2x thumb
YES, YES, YES!   Thanks so much!