# How to extract the decimal fraction of a number for a formula?

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?

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

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.

• thx, I'll adapt a little bit. it's "3.04" and not "3.4" but that might not even matter. I altered the question to reflect. Aug 17 at 20:43
• Aug 17 at 20:44