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.

### Like this:

Like Loading...

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.

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.

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

could i do this but for years between dates versus the days between?

Yes, of course.

You could easily estimate weeks, months, years, decades, etc. You just need to divide the number of days by the average length your unit of measure in days. 7 days for a week, 30 days for a month, 365 days for a year, etc. Then taking the floor of the resulting value to drop any fraction and convert it back into an integer (since that’s how we generally express years, months, weeks).

https://developer.sugarcrm.com/2013/09/23/use-sugar-logic-to-display-the-difference-between-two-dates-in-months/

Try

`floor(divide(subtract(daysUntil($end_date),daysUntil($start_date)),365))`

Hi thank you for this help. Do you know how to calculate a end of the month date. EG: 17/05/2016 (17 may 2016) the end of the month date is 31/05/2016) ?

cheers 🙂