Is there a formula to shade each week differently?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

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. 

Photo of Greg

Greg

  • 264 Points 250 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of Xavier Fan

Xavier Fan, Champion

  • 340 Points 250 badge 2x thumb
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.