EXCEL FORMULA AND FUNCTIONS

Copper Contributor

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

4 Replies

@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.

@Hans Vogelaar 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

@roblaporte11 

I'm sorry, I don't understand your examples at all.