By using this website, you agree to our Terms of Use (click here)
Hi All,
I would like to add column for this status. However, there's might wrong with my coding since i can't found any data for Expired >= 2 Years and Expired <= 3 Months.
Expiry Status:
Expired < 2 Years
Expired >= 2 Years
Expired <= 6 Months
Expired <= 3 Months =IIf([IspkpCompanyOperatorLicense.EndDate] <Today() AND [IspkpCompanyOperatorLicense.EndDate]<DateAdd([IspkpCompanyOperatorLicense.EndDate] , 'y', 2 ), 'Expired< 2 Years', IIf([IspkpCompanyOperatorLicense.EndDate] <Today() AND [IspkpCompanyOperatorLicense.EndDate]>=DateAdd([IspkpCompanyOperatorLicense.EndDate] , 'y', 2 ), 'Expired >= 2 Years', IIf([IspkpCompanyOperatorLicense.EndDate] >=Today() AND [IspkpCompanyOperatorLicense.EndDate]<=DateAdd([IspkpCompanyOperatorLicense.EndDate] , 'd', 180 ) , 'Expired <=6 Months', IIf([IspkpCompanyOperatorLicense.EndDate] >=Today() AND [IspkpCompanyOperatorLicense.EndDate]<=DateAdd([IspkpCompanyOperatorLicense.EndDate] , 'd', 90 ), 'Expired <= 3 Months', 'False'))))
Thank you
Try this:
=IIf([IspkpCompanyOperatorLicense.EndDate] < DateAdd(Today(),'y',2), 'Expired more than 2 Years',
IIf([IspkpCompanyOperatorLicense.EndDate] < DateAdd(Today(),'d',180), 'Expired 6 months to 2 Years',
IIf([IspkpCompanyOperatorLicense.EndDate] < DateAdd(Today(),'d',90), 'Expired 3 to 6 Months',
IIf([IspkpCompanyOperatorLicense.EndDate] < Today(), 'Expired less than 3 Months', 'False'))))
I changed your labels so that they made more sense to me. To check for cutoff dates, you need to apply DateAdd formula to Today().
Here's an alternate version using Switch():
=Switch([IspkpCompanyOperatorLicense.EndDate] < DateAdd(Today(),'y',2), 'Expired more than 2 Years',
[IspkpCompanyOperatorLicense.EndDate] < DateAdd(Today(),'d',180), 'Expired 6 months to 2 Years',
[IspkpCompanyOperatorLicense.EndDate] < DateAdd(Today(),'d',90), 'Expired 3 to 6 Months',
[IspkpCompanyOperatorLicense.EndDate] < Today(), 'Expired less than 3 Months',
True,'False'))))
Online Members
