CODEWITHSUNDEEP
excelgoogle-sheetsexcel-formulagoogle-sheets-formulaarray-formulas
Simon GIS
Simon GIS

Reputation: 1055

Google Sheets or Excel Function to return date based on value

I have this table in Google Sheets (or excel). The year is the two last digit of my code.

           Code                Duration Months
    
  1     AC-26482-17              60          
  2     AC-26482-18              30         
  3

I would like to return the date in this format (If no data, just leave blanks).

       Code              Duration Months        Start         Expiration 
    
  1   AC-26482-17           60                01/01/2017      01/01/2022
  2   AC-26482-18           30                01/01/2018      01/07/2020
  3

Is there a way to achieve this?

Upvotes: 0

Views: 82

Answers (2)

player0
player0

Reputation: 1

use:

=ARRAYFORMULA(IF(A2:A="",,DATE(IF(RIGHT(A2:A, 2)*1>=40,,20)&RIGHT(A2:A, 2), 1, 1)))

enter image description here

=ARRAYFORMULA(IF(A2:A="",,DATE(YEAR(C2:C), MONTH(C2:C)+B2:B, 1)))

enter image description here

Upvotes: -1

teylyn
teylyn

Reputation: 35915

You mean you want to add the duration in months to the start date? If so, your sample has the wrong expiration date. 30 months added to 1/1/2018 is not June 1st, but July 1st.

The formula in Excel is

=EDATE(C2,B2)

enter image description here

If you also want to calculate the start date from the last two characters of the code, given all dates are in this millennium, then you can use this for the start date:

=DATE(RIGHT(A2,2)+2000,1,1)

enter image description here

edit: To handle blank cells, you can check with IsBlank()

=if(isblank(a2),"",DATE(RIGHT(A2,2)+2000,1,1))

Upvotes: 5

Related Questions