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!