Can I use dates in another sheet to calculate the difference between two dates (NETWORKDAYS)?

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.

main sheet with current networkdays formula.png

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

All comments

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

@MarnieMaria

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.

https://community.smartsheet.com/discussion/comment/379267#Comment_379267

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

-Marnie