Formula to display duration in days and months

Ask the community Formulas
#1

Hi!

I am working on a contract template, where one of the dynamic fields pulled from AT is the duration of the contract, expressed in the following manner:

The initial term of this Agreement shall be for a period of 2 months and 5 days, commencing on…

I have a basic formula that allows me to extract an amount of time as a single term (1 month), but it’s not smart enough to say something like 1 month and 5 days. Basically, I don’t know how to add the remaining days. Here is my current formula:

IF(
   {End Date & Time}="",
   "",
   IF(
      DATETIME_DIFF(
         {End Date & Time},
         {Start Date & Time},
         'months'
      )=1,
      DATETIME_DIFF(
         {End Date & Time},
         {Start Date & Time},
         'months'
      ) & " month",
      IF(
         DATETIME_DIFF(
            {End Date & Time},
            {Start Date & Time},
            'months'
         )=0,
         IF(
            DATETIME_DIFF(
               {End Date & Time},
               {Start Date & Time},
               'days'
            )=0,
            DATETIME_DIFF(
               {End Date & Time},
               {Start Date & Time},
               'hours'
            ) &" hours",
            DATETIME_DIFF(
               {End Date & Time},
               {Start Date & Time},
               'days'
            ) &" days"
         ),
         DATETIME_DIFF(
            {End Date & Time},
            {Start Date & Time},
            'months'
         ) &" months"
      )
   )
)

Let me know if this doesn’t make sense, thanks!
Sean

#2

Hey Sean, hm, I’m not really following I’m afraid. Any chance you could give me a screenshot of your base set up or a specific example with the data you’re passing in and the output you’d want?

#3

Hi Adam,
Thanks for your reply. Basically, I have an agreement between two parties where a contract will be generated and signed. The agreement has a start date and end date, and I want to be able to dynamically add to the contract the duration, written out using words. So:

  • Contract is from April 1st to May 15th
  • The contract duration is 1 ½ months
  • I want that to be written out in the contract like this: 1 month and 15 days

Currently the above formula can handle 1 term, like “1 month” or “20 days”, but it can’t break down a period longer than a month into its smaller parts (as above).

Let me know if that clears things up.

Thanks!
Sean

#4

Ah, thanks for explaining everything! Check this out

I think this might solve your problem?

#5

Yes it does! I’m taking a crack at figuring this out based on how you’ve set it up, but let me know if you want any kind of tip to get access to the formula. Thanks again!