# Date Duration Formula Counting Number of Days is Producing a Negative Number

• 0
• Question
• Updated 4 years ago

This is the formula I am using:

ToDays([Line Item Start Date]-[Line Item End Date])

1. Why is it coming up with a negative number? And how can I prevent this?

2. Do I really need that +1 at the end?

Thanks so much!

• 354 Points

Posted 4 years ago

• 0

QuickBaseCoach App Dev./Training, Champion

• 65,422 Points
no problem,

just flip it around

ToDays([Line Item end date]-[Line Item start date])

Its the same in Excel.

You would add 1 if you wanted to count both ends of the date. e.g. you start and end on the same day. Is that a duration of zero days or 1 day in your mind.

ToDays([Line Item end date]-[Line Item start date]) +1

• 354 Points
This worked (also checked on user input and cleared up another problem) but there is an issue on line items that span one day, here is the full formula:

([Line Item Budget]) / (ToDays([Line Item End Date]-[Line Item Start Date]) +1)

In this case the Budget = \$10,000 and the Dates are from 4/15 - 4/15

Per my logic this would show 1 day with the value of \$10,000 per day. But instead I think it is pulling 0 because the value per day = -\$0.01

Any thoughts?
• 354 Points
There is also one other value also pulling weird and those dates are 11/1/15 - 11/30/15 we are getting -693,931 days. Any clue why?

QuickBaseCoach App Dev./Training, Champion

• 65,422 Points
Are these fields that you are working with date fields or date/time fields?  ie the start and end date fields.
• 354 Points
Just date fields

QuickBaseCoach App Dev./Training, Champion

• 65,422 Points
Can you post the formula which is giving the -693,931 days?  its a different formula, right, as this one you posted is a cost per day formula.
• 354 Points
I pulled out the day formula to see if that was what was causing the issue, but it's the same formula piece:
ToDays([Line Item End Date]-[Line Item Start Date])+1
• 354 Points
I also get that same wacky number for the 4/15 line item - but all other line items are calculating correctly

QuickBaseCoach App Dev./Training, Champion

• 65,422 Points
well, all I can suggest is that QuickBase does not have bugs for a simple formula like that, so the data in those two fields must be wonky.

QuickBaseCoach App Dev./Training, Champion

• 65,422 Points
Either QuickBase support or someone on this formula would have to have a look at your app.  It seems impossible for just a couple of record to have weird results if the data is valid in those records.  You can contact me via my profile if you want me to have a quick look.
• 354 Points
Thank you for your support. Those two records were wonky, I have no idea why. I am recreating them and the formula works fine, but I will keep a lookout to see if this continues happening.