Calculating Number of days between dates

Hi,

I am trying to figure out if there is a way to calculate the number of days between two dates, for e.g. I have a field called “project start date” and another “Project Age”. In project age, I want to show the number of days the project has been running for, i.e. the different between project start date and “today’s date”.

Wonder if its possible to do this?

Thanks

Hello,

You will probably want to address this in 2 ways. You will want a before_save logic hook to address new records. But this only performs when a record is saved so you will also need a scheduled job that runs a database query to do this calculation in the background.

Shad

Hi,

The date a project starts is automatically saved, so I am assuming I need to run a scheduled job once a day which calculates the number of days from the start date and then saves that data in the field?

Do you need the date difference to be stored, or is it enough to just display it?

I’m asking because it’s a lot simpler to just edit the DetailView (and perhaps the list view) to make the calculation on the moment - if that’s enough for your needs.

I’m using a similar solution to display my Contact’s ages. The database only stores the birth-date, then the age is calculated and shown every time a record is being displayed.

Hi,

can you please explain how to do this? I am floundering with calculating fields and I wish to do exactly this, calculate Contact’s ages.

Hey Mark,

You will need a calculated field something like:
{datediff({P0}; {P1}; days)}
Where P0 is the parameter from a date field and P1 is today. You should be able to do something like the formula below but I kept getting an error in the log related to creating the datestamp. You would need a job or work flow to set a date field to the current date first which should be able to be done in workflows but the trick is you need the current date updated first.

{datediff({P0}; now(Y-m-d H:i:s); days)}

While this is a common use case I am not sure if a Workflow/Calculated field is the best method to accomplish it. In my opinion a scheduled job that updates the field one time per day via a query would be best. Or as previously suggest just perform the calculation on render of detail or list view.

2 Likes

If you prefer the other solution overriding the detail view, I posted about it here

1 Like

Thank you for your help. It is much appreciated and is getting me closer to what I require.

thank you, I will investigate this. Your assistance is greatly appreciated.

1 Like