Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I initially tried achieving this through nested if, but it was not returning the desired results.
=if(WhenDate>MakeDate(year(Today()),Month(addmonths(today(),-3)),day(today())),
'Past Three Months',
if(WhenDate>MakeDate(year(Today()),Month(addmonths(today(),-6)),day(today())),
'Past Six Months',
if(WhenDate>MakeDate(year(Today()),Month(addmonths(today(),-12)),day(today())),
'Past Year',
if(WhenDate<MakeDate(year(Today()),Month(addmonths(today(),-12)),day(today())),
'Before a Year ago',
'')
)
)
)
I then saw a pick and match, but that doesn't work either.
=Pick(
Match(WhenDate,'Three Months','Six Months','Year','Year Beyond'),
WhenDate<addmonths(today(),-3),
WhenDate<addmonths(today(),-6),
WhenDate<AddYears(today(),-1),
WhenDate>AddYears(today(),-1)
)
Any suggestions on how to get this in a filter pane would be great.
Thanks in advance.
How about this?
If(WhenDate >= AddMonths(Today(), -3), 'Past Three Months',
If(WhenDate >= AddMonths(Today(), -6), 'Past Six Months',
If(WhenDate >= AddYears(Today(), -1), 'Past Year','Before a Year Ago')))
How about this?
If(WhenDate >= AddMonths(Today(), -3), 'Past Three Months',
If(WhenDate >= AddMonths(Today(), -6), 'Past Six Months',
If(WhenDate >= AddYears(Today(), -1), 'Past Year','Before a Year Ago')))
Apologies for the overcomplicated code as it was a long day. I initially convert to the addmonths/addyears after this post. This solution worked.