Calculate Date Difference in Data Table

Forms SharePoint Online
1

Hello Plumsail,

I'm trying to calculate the date difference between two dates but it's returning a value I don't understand.

image
image857×325 11.5 KB

I'm using a calculated column field and added this simple formula: EndDate - StartDate

I also think it might be worth adding the solution to your documentation for future users.

2

Hi @DryChips,

Any date in JavaScript is stored as the number of milliseconds that have passed since 00:00 January 1, 1970. Subtracting two dates gives you the number of milliseconds between them.

Make the column a Number and use this formula instead:

roundTo((EndDate - StartDate)/86400000, 0)
1 Like
3

Okayyyy, good to know. Thank you!

Can I ask where you learnt this from so I can read a little more?

4

Hi @DryChips,

This is general JavaScript knowledge unrelated to Plumsail Forms, so there is no article in our documentation detailing this. You should be fine without knowing all the ins and outs; making forms usually doesn't require such knowledge, and if you ever face a problem that does, we're here to help.

I can recommend this site if you want to read up on the topic. Their JavaScript guide also seems great.

1 Like
5

Hiya,

Is there anyway I can set the duration to 1 if the start and end dates are the same?

image
image795×218 6.85 KB

6

@DryChips,

You can add a condition that if dates are equal set the column value to 1.