Generic Inquiry/Reports - Counting how many of a day is in an arbitrary date range for KPIs

Hi everyone.

I spent quite awhile figuring this out, and I want to share the result in case it helps anyone out there.
 

Formula 

=Iif(DayOfWeek(@Start) + Iif(DayOfWeek(@Start)<=@TargetDayOfWeek,7,0)+(DateDiff( 'd', @Start, @End) % 7)>=(7+@TargetDayOfWeek),1,0) + Floor(DateDiff( 'd', @Start, @End)/7)

The above formula does it for any day of the week.

Why?

I wanted to be able to count how many hours someone was scheduled to work based on their work calendar between any 2 arbitrary dates.

The problem is, the CSCalendar table doesn’t have a direct relationship to Dates, and I my Project Transaction data could have multiple entries per day (So I can’t easily sum by connecting the project or timecard records)

It’s easy to figure out full weeks, it’s less easy to figure out the overhanging days...

Example usage - How many hours was someone scheduled for between [DateFrom] and [DateTo]

=(Iif((DayOfWeek([DateFrom]) + Iif(DayOfWeek([DateFrom])<=2,7,0)+DateDiff( 'd', [DateFrom], [DateTo])%7)>=(9),1,0)* Max([CSCalendar.TotalMonWorkHours])
+Iif((DayOfWeek([DateFrom]) + Iif(DayOfWeek([DateFrom])<=3,7,0)+DateDiff( 'd', [DateFrom], [DateTo])%7)>=(10),1,0)* Max([CSCalendar.TotalTueWorkHours])
+Iif((DayOfWeek([DateFrom]) + Iif(DayOfWeek([DateFrom])<=4,7,0)+DateDiff( 'd', [DateFrom], [DateTo])%7)>=(11),1,0)* Max([CSCalendar.TotalWedWorkHours])
+Iif((DayOfWeek([DateFrom]) + Iif(DayOfWeek([DateFrom])<=5,7,0)+DateDiff( 'd', [DateFrom], [DateTo])%7)>=(12),1,0)* Max([CSCalendar.TotalThuWorkHours])
+Iif((DayOfWeek([DateFrom]) + Iif(DayOfWeek([DateFrom])<=6,7,0)+DateDiff( 'd', [DateFrom], [DateTo])%7)>=(13),1,0)* Max([CSCalendar.TotalFriWorkHours])
+Iif((DayOfWeek([DateFrom]) + Iif(DayOfWeek([DateFrom])<=7,7,0)+DateDiff( 'd', [DateFrom], [DateTo])%7)>=(14),1,0)* Max([CSCalendar.TotalSatWorkHours])
+Iif((DayOfWeek([DateFrom]) + Iif(DayOfWeek([DateFrom])<=0,7,0)+DateDiff( 'd', [DateFrom], [DateTo])%7)>=(7),1,0)* Max([CSCalendar.TotalSunWorkHours])
+Floor(DateDiff( 'd', [DateFrom], [DateTo])/7)*Max([CSCalendar.HoursPerWeek]))

Logic walkthrough/how it works:

Counting How many Tuesdays there are in an arbitrary date range for Acumatica:
You can work this out by:

Iif(DayOfWeek(@Start) + Iif(DayOfWeek(@Start)<=@TargetDayOfWeek,7,0)+(DaysInRange % 7)>=(7+@TargetDayOfWeek),1,0) + Floor(DaysInRange/7)

1. Take the Starting day's Day Of Week ordinal number
2. If that number is less than or equal to the Target Day of Week ordinal number, add 7 to it.
3. Add the modulus (remainder) of the Days in Range divided by 7 to this
4. And then compare it to the Target Day of Week + 7
5. And finally add the number of full weeks there are in the span.

Given variables:
@Start = 2-November-2026 / '2026-11-02'
@End = 19-November-2026 / '2025-11-19'
Then @DaysInRange = 17(There are 17 days after the first day)  - Calculated using DateDiff( 'd', @Start, @End)
I want to know about Tuesdays so @TargetDayOfWeek=3

We can get this by plugging in a Date Diff:
 

Iif(DayOfWeek(@Start) + Iif(DayOfWeek(@Start)<=@TargetDayOfWeek,7,0)+(DateDiff( 'd', @Start, @End) % 7)>=(7+@TargetDayOfWeek),1,0) + Floor(DateDiff( 'd', @Start, @End)/7)

Plug the dates in...

Iif(DayOfWeek('2026-11-02') + Iif(DayOfWeek('2026-11-02')<=@TargetDayOfWeek,7,0)+(DateDiff( 'd', '2026-11-02', '2025-11-19') % 7)>=(7+@TargetDayOfWeek),1,0) + Floor(DateDiff( 'd', '2026-11-02', '2025-11-19')/7)

Starting Day is a Monday, so
DayOfWeek('2026-11-02') = 2
The difference between 2 & 19 is 17, so the DateDiff( 'd', '2026-11-02', '2025-11-19') =17

Iif(2 + Iif(2<=@TargetDayOfWeek,7,0)+(17 % 7)>=(7+@TargetDayOfWeek) ,1,0) + Floor( 17/7) 

If we divide 16 by 7 we have 2 sevens and  3 leftover (the modulus /remainder , done using % )
(17 % 7) = 3
Floor() means round down

Floor(17/7) = 2

Iif(2 + Iif(2<=@TargetDayOfWeek,7,0)+(3)>=(7+@TargetDayOfWeek),1,0) + 2

Now, we need to know which day of the week we want, Acumatica counts days with Sunday as a 1, and Saturday as a 7

1. Sunday = 1
2. Monday = 2
3. Tuesday = 3
4. Wednesday = 4
5. Thursday = 5
6. Friday = 6
7. Saturday = 7 

So for Tuesday @TargetDayOfWeek=3

Iif(2 + Iif(2<=2,7,0)+(3)>=(7+3),1,0) + 2

2 is less than or equal to 2, so the second Iif statement is True, and becomes 7

Iif(2 + 7 +(3)>=(7+3),1,0) + 2

If we add up the sides, on the left we have 13 (2 + 7 + 3) on the right we have 10 (7+3)

Iif(13>=10 ,1,0) + 2

13 is greater than 10, so the Iif is true and we add 1 leaving us with 3 Tuesdays.