I have a start (3-23-20) and an end date (10-19-22). How do I make the calculation in X years, Y months, Z days? Thank in advance?
Hi there.
How precise do you need this to be? I remember Jeff sharing formulas for date calculations that were very precise. I’m offering a more approximate solution.
The example you gave
The formulae I used
You can play around with the math formulae FLOOR, CEILING and ROUND to round up or down the numbers.
My formulae are approximate because you can see that when you are close to a limit, depending on what the exact dates are, you might be slightly above or slightly below a limit, which could entirely change the FLOOR or CEILING values.
1 Like
It doesnt’t have to really precise. It’s about how long people keep a subscription. Months would be nice to start with. Thanks! I’ll have a look!
1 Like
Thanks a lot!
1 Like
How do you use ROUND in a formula? Thanks again!
For DurationMonths for instance:
ROUND(DD/30.42)
FLOOR(DD/30.42)
CEILING(DD/30.42)
Ah thanks so much! Learned again today!
1 Like
Last question. I think 
When I do ROUND what is normally 8:03:14 I would expect 8 but get 1. Is there a way to get 8?
Is 8:03:14 a DateTime basic column? What happens once you pass it through a template column to change the format? Once you get a decimal number, then maybe try ROUND?
(I’m not in front of a computer 
)
1 Like
I would do DurationDays by Math (DurationHours/24).
You use a template for DurationDays and to get rid of the : 's. What does your template look like?
No hurry!
@nathanaelb You use a template for DurationDays and to get rid of the : 's. What would your template look like? Thanks!
Ah wow! Didn’t know about this one!
Great!
Thanks a lot!
cc @nathanaelb
1 Like
DurationHours
I used a math column to determine DurationHours. The result is displayed in an hour:minutes:seconds format.
DurationDays
I used the template column to change the format. An integer is now displayed (instead of hours:minutes:seconds) and instead of the number of hours being displayed, it is the equivalent number of days.
With the number of days as an integer and math columns, you can then determine number of hours, weeks, months, years.
3 Likes
Now wait here just a darn second… when did this arrive?! I’m excited to see how it handles leap years!
2 Likes
This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.