# Formula To Return Age of Product in #.## format

Topic Labels: Formulas
Solved
2288 7
cancel
Showing results for
Did you mean:
6 - Interface Innovator

Hello! I recently got into canning (and general food preservation). I have created a database which helps me manage my inventory levels of products I have canned (Example: Jars of: Carrots, Green Beans, Jams, Jelly, etc). In my table where I record batch information - I am recorded the date I created that batch of product. I’d like to have a column (called Age) that returns the age of the product (batch) in #.## format. For example: If I created a batch of product today, it’s age should read: “0.00” meaning its 0 years and 0 months old. If I created a batch 1 year ago, I’d like it to say “1.00” because it’s a year old. If I created a batch ‘days short of it being 2 yrs old’, I’d like it to say “1.11” because it’s 1 year old and 11 months. Any help would be greately appreciated.

1 Solution

Accepted Solutions

And if you want two digits after the period, here’s the update for that:

``````DATETIME_DIFF(NOW(), Date, "years") & "." & REPT("0", 2-LEN("" & MOD(DATETIME_DIFF(NOW(), Date, "months"), 12))) & MOD(DATETIME_DIFF(NOW(), Date, "months"), 12)
``````
7 Replies 7
18 - Pluto

This should work:

``````DATETIME_DIFF(NOW(), Date, "years") & "." & DATETIME_DIFF(NOW(), Date, "months")
``````

Replace both instances of `{Date}` with the name of the date field where you store the date it was canned.

6 - Interface Innovator

Hi Justin,

This is similar to what I did earlier but it returns the same problem. For 13 months, it should read: “1.01” instead it says “1.13”. Any ideas?

6 - Interface Innovator

So for example, the only values it should ever be… are…

0.01
0.02
0.03
0.04
0.05
0.06
0.07
0.08
0.09
0.10
0.11
1.00
1.01
1.02
1.03
1.04
1.05
1.06
1.07
1.08
1.09
1.10
1.11
2.00
2.01
2.02
2.03
and so on…

Sorry. I’ll admit I was rushing a bit in my test and didn’t go very far back. Here’s an update that works correctly for old dates:

``````DATETIME_DIFF(NOW(), Date, "years") & "." & MOD(DATETIME_DIFF(NOW(), Date, "months"), 12)
``````
6 - Interface Innovator

Not a problem at all, I really appreciate the help! It works beautifully! Thank you so much!

And if you want two digits after the period, here’s the update for that:

``````DATETIME_DIFF(NOW(), Date, "years") & "." & REPT("0", 2-LEN("" & MOD(DATETIME_DIFF(NOW(), Date, "months"), 12))) & MOD(DATETIME_DIFF(NOW(), Date, "months"), 12)
``````
6 - Interface Innovator

Oh, perfect! I didn’t even catch that (yes, I wanted the two digits). I have updated the formula and it’s working great! Btw, saw you had a YouTube channel, I just subscribed! :slightly_smiling_face: Thanks again for all the help!