HOWTO: Calculate the number of days since a date

bsoremsugar —  February 16, 2012 — 5 Comments

Here’s an interesting one I came across in the forums the other day:

i have contact form and PURCHASE DATE Field as (Date) Type. where the user selecting a date in DD/MM/YYYY format .. 

now as soon as i fill out this contact form and then if il go to view this particular entry , i want to see the Label there showing the 

—————————————
CUSTOMER SINCE : (here i want to show the number or Days (i.e. 256days , 1year and 3Days) ) or something 
—————————————

i want to show this Result as per the PURCHASE DATE selection, if as an admin i update this PURCHASE DATE 
then automatically it will affect to CUSTOMER SINCE result as well .. 

but u know i dont have any idea .. how to do this .. 

can any sugar master help me how to do this .. pls help me ..

This is something rather trivial to do with Sugar Logic. Just add a new custom field to hold the calculated value, and then add this as the calculated field formula for the field:

concat(multiply(daysUntil($date_entered),-1)," days")

What this does is use the daysUntil() function call, which will return the number of days between now and a date in the future. Being a date in the past, it returns a negative number, so using the special properties of multiplying by a -1, we can change the number into a positive one which we can return to the form.

5 responses to HOWTO: Calculate the number of days since a date

  1. 

    What type should the new custom field be and what $date_entered is it comparing?  I added a new custom integer field to Accounts, added the function and then added it to the List View in the Accounts module, but it shows as blank for every account listed.  I assumed it would compare the date the account was created and convert to a positve number and show in the new custom field.  Did I do something wrong?

    • 

      Should be a text field, since the above formula lists out ‘Days’ specifically in the returned string.

      The $date_entered would be the date_entered for the current record.

  2. 

    Hello John, Could you explain how we would calculate the difference between two dates? We’d like to know how many days there are between $date_entered and $date_modified. Also, is there a benefit to multiplying by -1 versus using the abs function?

    • 

      As for the first question, try this..

      subtract(daysUntil($date1), daysUntil($date2))

      And yes, you could have done abs() instead here, no real difference.

  3. 

     Hello John , could you please explain where to add the this custom code

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s