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

[Jason_Burke1]

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.

[Justin_Barrett]

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.

[Jason_Burke1]

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?

[Jason_Burke1]

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…

[Justin_Barrett]

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)

[Jason_Burke1]

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

[Justin_Barrett · Accepted solution]

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)

[Jason_Burke1]

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!