Count days between start and end date

Blessing Ugbo

I want to show count of days between start date and end date 

Mike Wilday

Do you want calendar days? or Net Work days? 

 

For Net Work Days
=Networkdays([start date]1, [end date]1) 

 

For Calendar Days

=Netdays([start date]1, [end date]1)

 

 

adamsker44426

I just tried this formula and the result was incorrect. I used the Netdays formula and the two dates were 5/29/18 and 5/30/18. The result should be "1" but it's "2." Thoughts?

J. Craig Williams

Adam,

I don't use NETDAYS as it is confusing and not documented well.

The description says "Returns the number of days between two dates"

This is, to me, misleading.

It should read "Returns the number of days between two dates, inclusive"

That is, both the 29th and the 30th are days in the range, so they get counted.

Imagine if you have a task that started on Wednesday (the 29th) and ends on Thursday (the 30th). That could conceivably give you the following to perform:

- 48 hours (start and end at midnight)

- 16 hours (two 8 hour days)

- 8 hours (starts at close of business on Wednesday)

and a whole range of between 0 and 48 hours.

The actual value is in the eye of the beholder.

If you think it should be 1, then either

=NETDAYS(date1, date2) - 1

or

=date2 - date1

Craig

(date1 and date2 are not cell references, they are just names and invalid ones at that)

Johnny

NETDAYS(TODAY(), TODAY())  returns 1

I don't understand the logic behind this.

 

 

 

J. Craig Williams

I will try to explain again, and if that does not help, perhaps someone else will chime in. (Older threads are only monitored by older Community members, so fewer read this)

NETDAYS concept is based in accounting and/or effort management.

If your boss tells you have you 2 days to get something done on Thursday morning, you probably will have Thursday and Friday (net days = 2) to get it done.

If he tells you the same thing on Thursday as you walk out the door heading home, you may rightly believe you must deliver on Monday, unless Saturday is a working day.

When the Cost Fairy hears "two days", she likely thinks 16 man-hours * $XX/hr = $16*XX

NETDAYS is based on effort, not calendar. 

It is also a viewpoint. 

I hope this helps.

Craig

Mike Wilday

Gotta love the cost fairy. As J.Craig said earlier in this very post. Use -1 to put her in her place. 

J. Craig Williams

I would go so far as to say that NETDAYS() is used when one expects an inclusive result and Date1-Date2 used when it isn't. 

Craig

Jose Munoz

Guys, do you have any idea on how to set this formula as default on the field?

For example:

I created a form to collect new projects request, we are using the formula in this post to calculate how many days we have to complete the project and created a column named Days till Completion but the issue is that if I copy the Formula on the other rows I get #INVALID DATA TYPE because there are no dates present, so I decided to open the FORM editor and entered the formula on the filed in the form as hidden with the idea of  when the form is entered the formula is also entered on the filed but instead of showing me the calculated numbers or the formula result I get the actual formula instead. 

Any idea on how to avoid this? 

Mike Wilday

Have you considered enabling dependencies and having Smartsheet do the calculations for you? When dependencies are enabled and you have a working duration column it will automatically calculate the number of days based on your dates. And when you expect the duration to go longer, you can adjust the duration and the finish date will adjust for you. (See screenshot below.)

To enable dependencies right-click on a header title and choose: Edit Project Settings. Click enable dependencies, set your start and finish columns and then Smartsheets will create your duration and predecessor columns for you and add them to the end of your sheet. 

For more on enabling dependencies see: 

• https://help.smartsheet.com/articles/765727-enabling-dependencies-using-predecessors