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

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

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!

Photo of Leanne

Leanne

  • 354 Points 250 badge 2x thumb

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
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





Photo of Leanne

Leanne

  • 354 Points 250 badge 2x thumb
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?
Photo of Leanne

Leanne

  • 354 Points 250 badge 2x thumb
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?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
Are these fields that you are working with date fields or date/time fields?  ie the start and end date fields.
Photo of Leanne

Leanne

  • 354 Points 250 badge 2x thumb
Just date fields
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
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.
Photo of Leanne

Leanne

  • 354 Points 250 badge 2x thumb
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
Photo of Leanne

Leanne

  • 354 Points 250 badge 2x thumb
I also get that same wacky number for the 4/15 line item - but all other line items are calculating correctly
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
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.
Photo of Leanne

Leanne

  • 354 Points 250 badge 2x thumb
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.