Discussions

Expand all | Collapse all

Formula / Report if this week is between two dates

  • 1.  Formula / Report if this week is between two dates

    Posted 03-20-2019 22:02
    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!


  • 2.  RE: Formula / Report if this week is between two dates

    Posted 03-20-2019 22:44
    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


  • 3.  RE: Formula / Report if this week is between two dates

    Posted 03-20-2019 23:14
    Thank you!  This worked perfectly!  Thank you again.