Networkday

Kirkk

Hello all - first time poster

I am trying to get the difference between two dates by using the NETWORKDAY formula and I am having an issue. I have 2 date field columns - an assignment date and a go-live date - and I am trying to find the difference in business days between the two. I have successfully run the formula to get the difference from the top date in each column so I know it works but when I select multiple dates - IE copying down each column for multiple dates - I get a #INVALIDDATATYPE. Does anyone know if this formula is limited to only one data set or can it be used for multiple dates in a column? This is the existing string I am using to get the error - =NETWORKDAY({Enterprise Client Tracker Range 18}, {Enterprise Client Tracker Range 19}) and this is the string i am using to get a successful response with only one date - =NETWORKDAY({Enterprise Client Tracker Range 16}, {Enterprise Client Tracker Range 17})

Any thoughts or suggestions?

Thank you in advance

 

ricki

Is the issue that you are getting the wrong answer or is the issue that you are not getting any answer at all and just an error.

 

If it is the latter the issue may be your use of {} for the fields instead of the []. I just tried the following and it worked as expected for me

 

=NETWORKDAY(TestDate3, [Due Date]21)

ricki

Sorry, i just reread your post again

 

Why are you trying to do a range? this formula is to find the difference in 2 days? For any one row wouldnt you want the difference in the 2 days for that one row. This would not use a day but just the relative row number in the formula when specifying the two dates

 

ie row 1 should be =NETWORKDAY({Enterprise Client Tracker Range 1}, {Enterprise Client Tracker Range 1})

 

and row 2 should be =NETWORKDAY({Enterprise Client Tracker Range 2}, {Enterprise Client Tracker Range 2)

 

etc

ricki

Or is the issue that your error formula has a space between the 1 and the 8?

Kirkk

Hi thanks for the response. So, I'm trying to take 2 columns and the associated dates from those columns and determine the difference between each set of dates. I have a screen shot attached but basically i need to find the duration of time between "assignment date" and "go-live" date for each set of records. Then, I am going to take those numbers and calculate an average to determine what the average turn around time is between assignment and go-live for all projects. For transparency i am already using the duration column for another calculation so I have to come up with a custom string to determine the duration of these dates.

I hope that makes more sense and can find a way to perform this.

Thank you!

ricki

Smartsheet doesnt really let you paste in screenshots so easily (even though in the box it looks like it does) so i couldnt really see your screenshot

 

I created a sheet where i have an "Assignment Date" column and a "Go Live Date" column

For each row of data i also have a column "Difference" and then i have one cell at the top of the sheet which is for average

In each row i have the following formula in the difference column 

=NETWORKDAY([Assignment Date]2, [Go Live Date]2)

Then in the one cell for the average i have =AVG(Difference2:Difference10)

 

you could also put that exact formula (same row numbers) into a column for each row if you are looking for the average to appear on every row

 

Is that what you are looking to do or is there something different that this does not cover?