Calculating the number of days between two date cells returning an extra day

RNelson

I am trying to calculate the number of days between two date cells and I am using the following formula. I put the -1 day at the end so that if the dates are the same it will count as "0" days instead of "1" day. However, if the end date is before the completion date it changes the formula to "+1" instead of "-1" day. Any idea how to prevent this from happening?

=IFERROR(NETDAYS([Date Completed]2, [Need By Date]2) - 1, "")

Paul Newcome

Here is how NETDAYS works:

 

Returns the number of days between two dates. Doesn't take in consideration weekends or holidays. Will produce a negative number if date_1 is chronologically after date_2.

 

If all you need is the number of days between 2 dates as a positive number, you could use

 

=ABS([Date Completed]@row - [Need By Date]@row)

RNelson

Thanks Paul. I do need to see the negative numbers as well so that I can see if I am over or under the goals. The formula I was using works...but it counts both the beginning date and the end date, so it is adding extra days.

Paul Newcome

Try getting rid of the NETDAYS function and see how that does then.

 

=[Date Completed]@row - [Need By Date]@row

 

This will basically take the day numbers for both dates and subtract them, so additional days shouldn't be an issue.

 

So today is the 22nd of February. That is the 53rd day of the year. Lets say the other date is the 27th of February (58th day of the year). It will basically subtract 53 from 58 (or the other way around if the dates were reversed).

 

Does this help?

RNelson

That did it, thank you very much for your help Paul!!

Paul Newcome

Happy to help.