Report on Recurring Dates Annually?
I am trying to create a report that shows "Birthdays within the next 7 days" and "Anniversaries This Quarter" and I am hitting a brick wall due to the "year" part of the DOB/Start Date fields. Any sug...
Forum Discussion
I have this formula duration field for the annual birthday question:
var date thisyear = Date(Year(Today()),Month([Birth Date]),Day([Birth Date]));
var date nextyear = Date(Year(Today()) + 1,Month([Birth Date]),Day([Birth Date]));
If($thisyear >= Today(),
$thisyear - Today(),
$nextyear - Today())
Similarly, this can be used to flag the quarter if the quarter of 'Start date' is the same as the quarter of Today():
var number startqtr = If(Month([Start Date])<3,1,If(Month([Start Date])<6,2,If(Month([Start Date])<9,3,4)));
var number thisqtr = If(Month(Today()) < 3,1,If(Month(Today()) < 6,2,If(Month(Today()) < 9,3,4)));
If($startqtr = $thisqtr,1)
var date thisyear = Date(Year(Today()),Month([Birth Date]),Day([Birth Date]));
var date nextyear = Date(Year(Today()) + 1,Month([Birth Date]),Day([Birth Date]));
If($thisyear >= Today(),
$thisyear - Today(),
$nextyear - Today())
Similarly, this can be used to flag the quarter if the quarter of 'Start date' is the same as the quarter of Today():
var number startqtr = If(Month([Start Date])<3,1,If(Month([Start Date])<6,2,If(Month([Start Date])<9,3,4)));
var number thisqtr = If(Month(Today()) < 3,1,If(Month(Today()) < 6,2,If(Month(Today()) < 9,3,4)));
If($startqtr = $thisqtr,1)