Forum Discussion

SaarLitman2's avatar
SaarLitman2
Qrew Member
5 years ago

Help with Date Formula

I'm trying to create a formula to only calculate if the [Eval Date] is between the [Start Date]  and [End Date]. 
My formula need to evaluate if they are in the same month not taking into consideration days. 

My current formula is:
If (Month([Start Date]) <= Month([Eval Date]) and Month([End Date]) >= Month([Eval Date]) and Year([Start Date]) <= Year([Eval Date]) and Year([End Date]) >= Year([Eval Date]),"Do something", "Don't do something)

This formula works as long as all dates are in the same year.. but fail when [Start Date] and [Eval Date] are in different years. 

I also tried a simpler solution of:
If ([Start Date] <= [Eval Date] and [End Date] >= [Eval Date],"Do something", "Don't do something)

This formula will work most of the time, but will fail for situations that [Start Date] and [End Date] are a few days apart. 

Any help will be appreciated.

------------------------------
Saar Litman
------------------------------
  • try this

    FirstDayOfMonth([Eval Date]) = FirstDayOfMonth([Start Date])

    That will be true if both dates are in the same month.


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • SaarLitman2's avatar
      SaarLitman2
      Qrew Member
      So Simple.. 

      Works perfectly,
      Thanks.

      ------------------------------
      Saar Litman
      ------------------------------