Is there a formula to shade each week differently?

• 0
• Question
• Updated 3 years ago

We pay invoices for referrals that are hired by Friday of each week.  On our Hire Report, I would like to shade each week differently.  For example, records with the Hire Date from Saturday 03-12-16 through Friday 03-18-16 would be shaded differently that records with the Hire Date from Saturday 03-19-16 through Friday 03-25-16.

• 264 Points

Posted 3 years ago

• 0

Xavier Fan, Champion

• 410 Points
Create a new Formula Date field, called [Hire Week], with the following formula:

var Date FirstFridayOfMonth = FirstDayOfMonth([Hire Date]) + Days( 5 - DayOfWeek(FirstDayOfMonth([Hire Date])) );

If (

[Hire Date] <= \$FirstFridayOfMonth, 1,

[Hire Date] <= \$FirstFridayOfMonth + Weeks(1), 2,

[Hire Date] <= \$FirstFridayOfMonth + Weeks(2), 3,

[Hire Date] <= \$FirstFridayOfMonth + Weeks(3), 4,

[Hire Date] <= \$FirstFridayOfMonth + Weeks(4), 5,

0)

First - FirstFridayOfMonth gives you the first Friday of the month, based on what day of week the 1st of the month happens to fall on.

Then, you can assign a "week number" (1, 2, 3, 4, 5) for each [Hire Date] - based on whether the [Hire Date] falls between Saturday, and the Friday of the following week.

Now, in your report settings, go to Options > Record highlight and use the following formula:

If (

[Hire Week] = 1, "Red",

[Hire Week] = 2, "Yellow",

[Hire Week] = 3, "Orange",

[Hire Week] = 4, "Blue",

[Hire Week] = 5, "Green"

)

You can substitute the "named" colors with hex color values (e.g. "#666666") as desired.