What is the formula for determining if a date falls within the following calendar month?

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

I have created a monthly report that shows all the jobs that need to be scheduled for the following month.  I've pulled in a "Most Recently Scheduled" date field for work that has already been scheduled.  I would like to highlight the jobs that have not been scheduled for the following month.  For example, my current report shows all jobs that need to be scheduled every January.  Some of the "Most Recently Scheduled" dates will be in the past for the last time we performed the job and some will be in January 2016 for those we've already scheduled.  I'd like to highlight the jobs that do not have a date in the following month and still need to be scheduled but I can't figure out how to do it.

Photo of Terri

Terri

  • 70 Points

Posted 3 years ago

  • 0
  • 1
Photo of Xavier Fan

Xavier Fan, Champion

  • 340 Points 250 badge 2x thumb
Try this:

Create a Formula-Checkbox field called [Scheduled for Following Month], with the following formula:

var Date FollowingMonth = AdjustMonth(Today(), 1);

Year([Most Recently Scheduled]) = Year ($FollowingMonth)


and

Month([Most Recently Scheduled]) = Month($FollowingMonth)



The Date variable FollowingMonth calculates the following month - by going 1 month in the future from today's date.  If the way you determine "following month" is different, you can adjust it here.

Then it just checks that the year AND month for [Most Recently Scheduled] is the same for $FollowingMonth - meaning that the most recently scheduled job falls in the same year and month as the following month.
Photo of Terri

Terri

  • 70 Points
Thank you so much! Worked like a charm!
Photo of Xavier Fan

Xavier Fan, Champion

  • 340 Points 250 badge 2x thumb
Great - happy to help!