Expression for Total Days

I created a Conference Request form (Confi Forms) and am using a Simple Date field type to capture the startdate and enddate (field names). I would like the next field (totaldays) to auto populate the total days.

startdate to enddate = totaldays (how many days our employee will be gone to the conference)

I set totaldays to a Calculated field type and am hoping there's a simple expression that I can add to do the math.


1 answer

1 accepted

2 votes
Answer accepted
Davin Studer Community Champion Jun 09, 2016

If your users might edit the entry after it is created then you would want to use a formula type instead. Calculated fields are only evaluated on creation of the entry. Formula fields are evaluated on every save. Anyway, the expression to use is below.

(([entry.enddate]-[entry.startdate])/86400000) + 1
Davin Studer Community Champion Jun 09, 2016

FYI, the reason for this is that the dates are stored on the backend as milliseconds since January 1, 1970. So, you can subtract the two numbers and divide by 86400000 (the number of milliseconds in a day) to get the number of days between start day 00:00 and end day 00:00. The +1 at the end is because the date is stored as the beginning of each day and you probably want them out all of the the end day ... hence the need to add 1.

Thank you! Front-end web and multimedia designer – very new to anything outside of HTML, CSS and light JavaScript.  Help is so, so appreciated.

Also – thank you for taking the time time to explain why.

Hi Davin. Would you be able to help me with a similar formula?

I'm attempting to subtract two time entries using the confiforms "calculated" field option. Working off of what you did I tried using this> "(([entry.Timeclientrequested]-[entry.firstattempt]) /3600000)" thinking I could use milliseconds in an hour and accomplish the same thing. No dice though, the format is AM PM for time, maybe thats throwing it off. 

This would be a great help. 

Davin Studer Community Champion Apr 06, 2018

I'll answer it over on your other question.

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Mar 12, 2019 in Confluence

Confluence Admin Certification now $150 for Community Members

More and more people are building their careers with Atlassian, and we want you to be at the front of this wave! Important Dates Start the Certification Prep Course by 2 April 2019 Take your e...

264 views 2 10
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you