As a follow-up to the other recent posts on variables I thought we could quickly walk-through two very useful functions for working with dates: DaysBetween() and RelativeDate(). Both are relatively easy to understand.
DaysBetween is used to calculate how many days there are between two dates. In the example below it is used to calculate the # of days between the Ship Date and the Arrival Date. Typically the earliest date is placed first within the function.
The number of days are provided in an integer, as you can see in the Days Between Ex column below. There is also a MonthsBetween() function available in Web Intelligence if you'd rather get the number of months between two dates.
The RelativeDate() function is similar except rather than providing two dates you enter one date and a positive or negative constant for how many days you want to add or subtract. For example, below we used RelativeDate() to subtract 28 days from today's date (utilizing the CurrentDate() function to get today's date). That provides the date four weeks ago.
The data is as follows in the 4 Weeks Ago column. It is the same for every row in this case because the formula is using the current date and subtracting 28 days. How is this useful? Well, in this case we can now leverage another formula to create a flag. The "Used in the Last 4 Wks" column compares the Last Used Dt to the 4 Weeks Ago date and flags those where the Last Used Dt >= 4 Weeks Ago.