Spreadsheet: 851792d1701928382-coupon-claims-date-to-be-next-date-with-the-help-of-formula-excel-problem-1-hd
Sheet: Sheet1

S.No. | Customer Name | Investment Amount | Issue Date | 1st Coupon | 2nd Coupon | 3rd Coupon | 4th Coupon | 5th Coupon | 6th Coupon | 7th Coupon | 8th Coupon | 9th Coupon | 10th Coupon | Maturity Date | Bond Nature | Next Coupon Date | Tenor | Status | Tenor (Months)
1 | [NAME] | 1000000 | 2020-10-10 00:00:00 |  | 2021-10-10 00:00:00 |  |  |  |  |  |  |  |  |  |  |  | 1-Year
 | Olivia | 5000 | 2020-12-31 00:00:00 |  | 2021-12-31 00:00:00 | 2022-07-01 00:00:00 | 2022-12-31 00:00:00 | 2023-07-01 00:00:00 | 2023-12-31 00:00:00 |  |  |  |  |  | Coupon Bond |  | 3-Years
 | [NAME] | 5000 | 2021-05-31 00:00:00 |  | 2022-05-31 00:00:00 | 2022-12-01 00:00:00 | 2023-05-31 00:00:00 | 2023-12-01 00:00:00 | 2024-05-31 00:00:00 | 2024-12-01 00:00:00 | 2025-05-31 00:00:00 | 2025-12-01 00:00:00 | 2026-05-31 00:00:00 |  | Coupon Bond |  | 5-Years
 | [NAME] | 100000 | 2021-08-30 00:00:00 |  | 2022-08-30 00:00:00 | 2023-03-01 00:00:00 | 2023-08-30 00:00:00 | 2024-03-01 00:00:00 | 2024-08-30 00:00:00 | 2025-03-01 00:00:00 | 2025-08-30 00:00:00 | 2026-03-01 00:00:00 | 2026-08-30 00:00:00 |  | Coupon Bond |  | 5-Years
 | Sophia | 500000 | 2022-01-25 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  | 3-Months
 | [NAME] | 5000 | 2022-03-31 00:00:00 |  | 2023-03-31 00:00:00 | 2023-10-01 00:00:00 | 2024-03-31 00:00:00 | 2024-10-01 00:00:00 | 2025-03-31 00:00:00 | 2025-10-01 00:00:00 | 2026-03-31 00:00:00 | 2026-10-01 00:00:00 | 2027-03-31 00:00:00 |  | Coupon Bond |  | 5-Years
 | [NAME] | 100000 | 2022-10-31 00:00:00 |  | 2023-10-31 00:00:00 | 2024-05-01 00:00:00 | 2024-10-31 00:00:00 | 2025-05-01 00:00:00 | 2025-10-31 00:00:00 |  |  |  |  |  | Coupon Bond |  | 3-Years
 | [NAME] | 500000 | 2023-03-28 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  | 6-Months
 | Emma | 100000 | 2023-05-02 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  | 3-Months
 | [NAME] | 500000 | 2023-08-17 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  | 6-Months
 | STATUS
 | Outstanding | Hold by Customer till Maturity date |  |  | BLANK |  |  | Please help me in attached sheet to fix coupon due date if following situation occurs:
 | Matured | Matured at the date of Maturity |  |  | MATURED
 | Pre-Matured | Pre-Matured by Customer before Maturity date |  |  | Pre-Matured |  |  | Coupon Claims Date to be next date with the help of formula
 |  |  |  |  |  |  |  | January | No issue | July | No issue | Match day and month only not year
 | CLASSIFICATION |  |  |  |  |  |  | 31 days |  | 31 days
 | Resident |  |  |  |  |  |  | Febraury | Coupon of 29,30 & 31 August will be paid on 1st March (Next Date) | August | No issue | IF any day & month of these three dates then | 29-08-0000 | Give Coupon Date like this date | 01-03-0000
 |  |  |  |  |  |  |  |  |  |  |  |  | 30-08-0000
 | Non-Resident |  |  |  |  |  |  | days 28 |  | days 31 |  |  | 31-08-0000
 | Resident & Non-Resident |  |  |  |  |  |  | March | No issue | September | Coupon on 31st March will be paid on 1st Oct  (Next Date) | IF day & month of this date then | 31-03-0000 | Give Coupon Date like this date | 01-10-0000
 |  |  |  |  |  |  |  | days 31 |  | days 30
 | CURRENCY |  |  |  |  |  |  | April | Coupon of 31st Oct   will be paid on 1st May (Next Date) | October | No issue | IF day & month of this date then | 31-10-0000 | Give Coupon Date like this date | 01-05-0000
 | USD |  |  |  |  |  |  | days 30 |  | days 31
 | EUR |  |  |  |  |  |  | May | No issue | November | Coupon of 31st May will be paid on 1st Dec (Next Date) | IF day & month of this date then | 31-05-0000 | Give Coupon Date like this date | 01-12-0000
 | GBP |  |  |  |  |  |  | days 31 |  | days 30
 | AED |  |  |  |  |  |  | June | Coupon of 31st  Dec will be paid on 1st July (Next Date) | December | No issue | IF day & month of this date then | 31-12-0000 | Give Coupon Date like this date | 01-07-0000
 | SAR |  |  |  |  |  |  | days 30 |  | days 31
 | TENORS
 | 3-Months
 | 6-Months
 | 1-Year
 | 3-Years
 | 5-Years