Can I use dates in another sheet to calculate the difference between two dates (NETWORKDAYS)?
I am trying to calculate the number of workdays between two dates, but the dates are in another sheet. I thought I could create a simple NETWORKDAYS formula referencing the two columns with the dates in the other sheet, but only get back an INVALID DATA TYPE error. Both referenced columns are DATE type, the column with the formula is Text/Number.
Then I tried to use the formula for specific cells and it works totally fine, so I figured my formula is wrong. I tried to add @row to the cross sheet column refrence, but it comes out UPARSEABLE. It's a very simple formula, though, so not sure what is UNPARSEABLE about it. And now I'm stumped. I feel like I'm overlooking something simple. Any idea where my mistake is?
=NETWORKDAYS({Sheet1ColA}@row, {Sheet1ColB}@row)
Thanks,
Marnie
Best Answers

Thank you for clarifying. I didn't realize both dates you were using were in the same sheet. I understand not wanting to make your sheets any larger with more columns.
I would create a separate metrics sheet for this if you have 15 different date columns that you are wanting to pull.
Formulas:
ColA column formula: =INDEX({One sheet two date  ColA  Report Sent internal}, MATCH([Project Number]@row, {one sheet two date columns Project Number}, 0))
ColB column formula: =INDEX({One sheet two date columns ColB  App Rcvd}, MATCH([Project Number]@row, {one sheet two date columns Project Number}, 0))
Networkdays: ColA  ColB column formula: =NETWORKDAYS(ColA@row, ColB@row)
Then add additional columns for the 15 columns you need.
Then in the main sheet that you have your current NETWORKDAYS formula in, us an INDEX MATCH formula to pull the correct info from you new metrics sheet.
This formula would need some tweaking if you are going to be using more date columns than the ColA & ColB in my example.
Will this work?
Peggy

Thanks again so much, Peggy! I see what you did there  it seems like this should work without having to create a separate sheet, but if I can't think of anything else at least I know I have a solution. I really really appreciate your help!
Marnie
Answers

Hello! In your two sheets, is it one row that you want to know the number of workdays between two dates or column to column?
Thanks Peggy

Hi Peggy,
It is column to column.
Thanks!
Marnie

In your two sheets, is there a common column they both have? Maybe a project name or number?
Thanks, Peggy

I think this will work provided there is a common column that exists in both sheet a & sheet b (maybe a project name or number or ???). This common column is the "Common Item" column below.
Sheet a
Sheet b
Create a new metrics sheet: Sheet a & b metrics
Formulas:
ColA column formula: =INDEX({Sheet a ColA}, MATCH([Common Item]@row, {Sheet a Common Item}, 0))
ColB column formula: =INDEX({Sheet b ColB}, MATCH([Common Item]@row, {Sheet a Common Item}, 0))
Networkdays column formula: =NETWORKDAYS(ColA@row, ColB@row)
In this solution, you would need to change the name of the Common Item column to whatever column exists in both sheet a & sheet b.
Hope this helps.
Peggy

Hi Peggy,
Thanks for the response! Yes, I do have a common column, a project number and that would work, but I need to compare dates in this manner for about 15 columns in the sheet that has an additional 15 columns, so about 30 columns total. Pulling in the dates for each comparison would mean that the sheet would grow to 45 columns or more and I definitely do not want that. Same for the original sheet, which is already at 90+ columns. I could just do the calculations there, but that would mean adding 15 columns to an already excessive sheet.
I would think there would be an easier solution, but mabe not...

Could you provide some screenshots of your two sheets that you are wanting to pull the data from  removing any proprietary information please?
Thanks Peggy

Do these help any? The first is the sheet with the formula, the second and third is the reference sheet dialog box that show where the dates are coming from.
This formula just returns UNPARSEABLE.

Could you clarify  is there more than one column that you want to pull dates from to determine NETWORKDAYS from two different sheets or is it just one column from two sheets? Is your common column, the project number, named the same in both sheets?
Thanks Peggy

Hi Peggy,
Both dates for the NETWORKDAYS formula are in another sheet (same sheet), so it is two columns I'm pulling. Yes, the common column is Project Number, named the same in both sheets.
Thanks again,
Marnie

Thank you for clarifying. I didn't realize both dates you were using were in the same sheet. I understand not wanting to make your sheets any larger with more columns.
I would create a separate metrics sheet for this if you have 15 different date columns that you are wanting to pull.
Formulas:
ColA column formula: =INDEX({One sheet two date  ColA  Report Sent internal}, MATCH([Project Number]@row, {one sheet two date columns Project Number}, 0))
ColB column formula: =INDEX({One sheet two date columns ColB  App Rcvd}, MATCH([Project Number]@row, {one sheet two date columns Project Number}, 0))
Networkdays: ColA  ColB column formula: =NETWORKDAYS(ColA@row, ColB@row)
Then add additional columns for the 15 columns you need.
Then in the main sheet that you have your current NETWORKDAYS formula in, us an INDEX MATCH formula to pull the correct info from you new metrics sheet.
This formula would need some tweaking if you are going to be using more date columns than the ColA & ColB in my example.
Will this work?
Peggy

Thanks again so much, Peggy! I see what you did there  it seems like this should work without having to create a separate sheet, but if I can't think of anything else at least I know I have a solution. I really really appreciate your help!
Marnie

Happy to help  have a great day!
Peggy
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 379 Global Discussions
 213 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!