You are viewing limited content. For full access, please sign in.

Question

Calculation of Days between two dates through excel formula

Laserfiche Forms Version 10

Updated October 4, 2016

asked on October 4, 2016 • Show version history

Hi all,

I have two Date fields and i want to calculate the number of days between the Beginning Date and Return Date. I have created a Number field, named as ‘Days Taken’ and default it to zero. Also, I have added a SUM(-Beginning_Date, Return_Date) in the Advanced tab. But, when I insert a Beginning Date, the ‘Days Taken’ is showing negative value. Is it possible to leave the field to zero till the user has input both dates.Can you please advise on how to solve it? Please see below screenshots.

negative value appearing.png (8.93 KB)

| Download

calc_days_taken.png (16.9 KB)

| Download

0 0

Replies

replied on October 4, 2016 • Show version history

Hi Sheila,

You can use DATEDIF() formula to calculate the days between two dates, example:

=(DATEDIF(Beginning_Date,Return_Date,"D")+1)

Note that:

We used format "D" to calculate the days.
We added +1 because the default calculation is excluding first day.

And for your question about how to leave Days Taken field to zero if one of the fields is empty you can use IF() with AND() to achieve this, for example:

=IF(AND(Beginning_Date<>"",Return_Date<>""),(DATEDIF(Beginning_Date,Return_Date,"D")+1),"")

Or if you want to use SUM() instead of DATEDIF() you can use:

=IF(AND(Beginning_Date<>"",Return_Date<>""),(SUM(-Beginning_Date,Return_Date)+1),"")

2 0

You are not allowed to follow up in this post.

Question

Question

Calculation of Days between two dates through excel formula

Replies

Sign in to reply to this post.