Forum Discussion

roblaporte11's avatar
roblaporte11
Copper Contributor
Feb 25, 2023

EXCEL FORMULA AND FUNCTIONS

Hi All,

 

Anyone can help for the below sample formula and functions as follows:

 

a) =IF(Start Date > Last Day of Year,0,(Amended End Date - Amended Start Date)/365) 
b) Amended End Date = IF(Expiry Date > Last Day of Year,Last Day of Year,Expiry Date)

 

Given scenarios as below:

Policy
Inception DateExpiry Date
26/02/202226/12/2022
05/26/202205/07/2022

 

Appreciate to anyone can help.

 

Sincerely yours,

Rob

excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 25, 2023

    roblaporte11 

    Your post has (at the moment of writing) 69 views and 0 replies, perhaps because it's difficult to understand.

    • Last Day of Year - which year?
    • Your example has Inception Date - is that the same as Start Date?
    • Your example doesn't show Amended Start Date and Amended End Date.
    • roblaporte11's avatar
      roblaporte11
      Copper Contributor
      Feb 26, 2023

      HansVogelaar thank you for your response

      Last Day of Year - which year? 31/12/2022
      Your example has Inception Date - is that the same as Start Date? - Yes and Expiry is the End Date
      Your example doesn't show Amended Start Date and Amended End Date. - ASD you can change the pervious sample data to become amended date i.e (26/04/2022 as Amended Start Date ASD based on previous sample) as for AED Amended End Date 26/11/2022) you can choose any date if you wish to have a workaround

      Giving the clear sample for the below functions
      a) =IF(Start Date 26/02/2022 > Last Day of Year is 31/12/2022,0,(Amended End Date is 26/11/2022 - Amended Start Date is 26/04/2022 )/365)

      Further sample (from here we need to get the Number of days, Active in year or months & person years covered)
      a) SD 05/06/2022 ED 05/07/2022 (policy period)
      01/02/2022 ASD Amended Start Date
      31/12/2022 AED Amended End Date
      a) IF SD < 1 Feb 2022, Amended SD = 1 Feb 2022, Else Amended SD = SD SD = 1 Feb 2022
      IF ED > 31 Dec 2022, Amended ED = 31 Dec 2022, Else Amended ED = ED ED = 31 Dec 2022

      we say:
      SD < ASD = False ED > AED = False
      Number of days
      =IF(SD>ASD,SD,ASD) note ASD the date you can convert into number
      =IF(ED>AED,ED,AED) note AED the date you can convert into number

      Active in years
      the result of Number of days it will become SD and ED these will SD - ED to get the active in years = 30

      Person years covered
      =IF(SD>AED,0,Active years is 30 / 365 = 0.08219

      Trust the above sample can clear your imagination, thanks again for your response.

      Rob

Resources