Forum Discussion

KimberlyGaston's avatar
KimberlyGaston
Qrew Cadet
7 years ago

Formula / Report if this week is between two dates

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!
Formulas and functions
reports and charts
  • ChayceDuncan2's avatar
    ChayceDuncan2
    Qrew Cadet
    7 years ago
    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