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

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭
    edited 06/07/23 Answer ✓

    @MarnieMaria -

    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

  • MarnieMaria
    MarnieMaria ✭✭
    Answer ✓

    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

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭

    @MarnieMaria -

    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

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭

    @MarnieMaria

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

    Thanks, -Peggy

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭

    @MarnieMaria -

    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...

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭

    @MarnieMaria -

    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.


  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭

    @MarnieMaria -

    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

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭
    edited 06/07/23 Answer ✓

    @MarnieMaria -

    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

  • MarnieMaria
    MarnieMaria ✭✭
    Answer ✓

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!