My WeekDaySub formula appears to have excluded Christmas Day 2015 but not New Years Day 2016?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
I have the following numeric formula in a parent table:

([Billable Hours Current Month Not CW]/WeekdaySub(PrevDayOfWeek(Today(), 6),FirstDayOfMonth(Today())))*WeekdaySub(LastDayOfMonth(Today()),FirstDayOfMonth(Today()))

The field [Billable Hours Current Month Not CW] is a summary field.  I'll give a quick example to explain what the formula does:  

Assume today is Jan 15, 2016.  
billable hours through Today = $2000
billable hours through the most recently past Saturday (1/9/2016) = $1500
# of business days prior to most recently past Saturday = 6 (Jan 1,4,5,6,7,8)
# of business days in the month = 21
The formula does this:  (1500/6)x(21)

My Question:  I've studied the resulting calculations very thoroughly, and I have determined that without any doubt, the formula counted 22 total business days in December 2015, but counted 21 in January.  Why?  Am I missing something?
Photo of Robin CC

Robin CC

  • 200 Points 100 badge 2x thumb

Posted 3 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
I have not looked at your formula, but just to be sure that you are aware of what the weekday Sub does - it counts weekdays like M T W Th and Fridays but not weekend days.  It has no awareness of holidays.
Photo of Robin CC

Robin CC

  • 200 Points 100 badge 2x thumb
I am aware, but I can't figure out why it counted 22 days in December and not 23.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Well, because WeekDaySub works like regular subtraction and perhaps differently from a similar a excel function.

For example. December has 31 days. But 31 - 1 = 30.

So you need to add 1 to count both the first and last day.