Hi all

I've searched online to see if an existing calculation has been made available online to achieve my goal here. I'm actually quite surprised I've been unsuccessful in finding one as I believe it would be a neat feature, though I can see why it may be considered complex.

I currently have two date fields (startDate, endDate) and I'm trying to calculate a plain-English read out of the time frame. For example:

(startDate) 01/01/2021 (endDate) 01/01/2021
"1 Day"

(startDate) 01/01/2021 (endDate) 01/03/2021
"3 Days"

(startDate) 01/01/2021 (endDate) 01/08/2021
"1 Week"

(startDate) 01/01/2021 (endDate) 01/09/2021
"1 Week, 1 Day"

(startDate) 01/01/2021 (endDate) 01/09/2022
"1 Year, 1 Week, 1 Day"

Is this possible or is it a long convoluted calculation? Would an API be better? I'm aware FM stores dates as numbers and the duration of months can vary (29, 30, 31 days...) and years can be 365/366 calendar days, etc. My overall plan here would be for the readout to be "smart" in that it only displays the applicable length of time, so no 0 Weeks, 0 Months, 0 Years inadvertently shown to the user.

Maybe start with this:

https://www.briandunning.com/cf/378

2 hours ago, madman411 said:

I believe it would be a neat feature, though I can see why it may be considered complex.

It's not that complex once it's clear what exactly you want to accomplish by it. The reason for this is that there is no correct solution to the problem. As you have noted, months do not have a constant number of days, so any calculation expressing a duration in years, months and days may be considered "correct" for some purpose, but invariably incorrect for other purposes. Replacing months with weeks does not change much, because a leap year has a different number of weeks than a regular year, and no year has an integer number of weeks. So no mater what method you choose, someone will be able to show how illogical its results are in some circumstances.

For example, compare these two calculations:
https://fmforums.com/topic/92520-age-from-date-of-birth-calculation-doesnt-work/?do=findComment&comment=424950&_rid=72594
https://fmforums.com/topic/89899-age-calculation/?do=findComment&comment=412505&_rid=72594

 

 

1 hour ago, Fitch said:

Maybe start with this:

https://www.briandunning.com/cf/378

I wouldn't.

 

Edited May 31, 20214 yr by comment

That was just by way of example.