0

For a column where years and months are entered as 1.02 for one year and two months, or 3.04 meaning three years and four months, how is that converted to a date?

Or just expressed so that it's base 10 correctly. Exactness preferred but not required.

Ideally, just convert to a date: ie, 3 years and four months (ago/from) todays date. Adding and subtracting dates is fairly straightforward, but these aren't exactly correct.

The fraction is base twelve, but of course it's seen as base ten.

Can the fraction simply be "marked" as base twelve?

1

Your post says that you want to be adding or subtracting such time periods from TODAY(), but it is unclear how you intend to do that structurally in the sheet.

That said, suppose you had 3.04 in cell A2. This would give you the date three years and four months ago:

=DATE(YEAR(TODAY())-INT(A2), MONTH(TODAY())-MOD(A2,1)*100, DAY(TODAY()))

If you wanted to move forward in time, you'd simply change the two instances of - to +.

If you wanted to find TODAY()-x for an entire range, say A2:A, where x represents your custom-style timeframes, you could place the following in, say, cell B2 of an otherwise empty Column B:

=ArrayFormula(IF(A2:A="",,DATE(YEAR(TODAY())-INT(A2:A), MONTH(TODAY())-MOD(A2:A,1)*100, DAY(TODAY()))))

Either of these formulas will return real, usable dates.

Using MOD( ,1) is one way of returning the decimal portion of a number, where MOD stands for "modulo" or "what is left over after dividing by some number."

1

Assuming that A2:A is the column where you are using the years.months notation, put this formula in row 2 of a free column:

=arrayformula( 
  if( 
    isnumber(A2:A), 
    date( 
      year(today()) + int(A2:A), 
      month(today()) + iferror(regexextract(trim(A2:A), "\.(\d+)")), 
      day(today()) 
    ), 
    iferror(1/0) 
  ) 
)

See this answer for an explanation of how date and time values work in spreadsheets.

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.