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.
Formula To Return Age of Product in #.## format
Best answer by Justin_Barrett
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)
+21This 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.
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.
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?
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.
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…
+21So 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)
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)
Not a problem at all, I really appreciate the help! It works beautifully! Thank you so much!
+21Not 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)
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)
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!
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.