Formula / Report if this week is between two dates

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • Answered
Hello.  I have a table of planned outages (for a utility company).  I want a report for all of the outages happening during the current week.  I have a start date and end date field.  Simply, I want to display those outages in which any day during the current week is between the start & end dates. 
For example, if a planned outage starts on March 1st and ends on April 30th, I would want it to remain on the report for all of the weeks that fall between those two dates, even if the person were to look at the report on Thursday May 2nd.  
Does that make sense?  Can anyone help me with a formula to display this?
Thanks!
Photo of Kim Gaston

Kim Gaston

  • 244 Points 100 badge 2x thumb
  • unsure

Posted 3 months ago

  • 0
  • 1
Ran a few tests of the below and I think it will work for your use case described. Essentially it reads that the First day of todays date has to be on or after the first day of the week when it starts, and the last day of the week of the outage has to be on or after the last day of the week when it ends. I ran a couple sim

Formula-checkbox

If(
    FirstDayOfWeek(Today()) >= FirstDayOfWeek([Start Date]) and
    LastDayOfWeek(Today()) <= LastDayOfWeek([End Date]),
true,false)


Chayce Duncan | Technical Lead
(720) 739-1406 | chayceduncan@quandarycg.com
Quandary Knowledge Base
Photo of Kim Gaston

Kim Gaston

  • 244 Points 100 badge 2x thumb
Thank you!  This worked perfectly!  Thank you again.