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
Accepted answer
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
Posted Oct 24, 2018 in Confluence

Atlassian Research opportunity with Confluence templates

Do you use templates with Confluence? Take part in a remote 1-hr workshop. You'll receive USD $100 for your time!   We're looking for people to participate in a   remote 1-hr workshop...

1,575 views 26 14
Join discussion

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