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

MarnieMaria
MarnieMaria ✭✭
in Formulas and Functions

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

· Share on FacebookShare on Twitter

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.

    NEW - metrics sheet.png

    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

    · Share on FacebookShare on Twitter
  • 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

    · Share on FacebookShare on Twitter

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

    · Share on FacebookShare on Twitter
  • MarnieMaria
    MarnieMaria ✭✭

    Hi Peggy,

    It is column to column.

    Thanks!

    -Marnie

    · Share on FacebookShare on Twitter
  • 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

    · Share on FacebookShare on Twitter
  • 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 a.png


    Sheet b

    sheet b.png

    Create a new metrics sheet: Sheet a & b metrics

    sheet a and b metrics.png

    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

    · Share on FacebookShare on Twitter
  • MarnieMaria
    MarnieMaria ✭✭

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

    · Share on FacebookShare on Twitter
  • 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

    · Share on FacebookShare on Twitter
  • MarnieMaria
    MarnieMaria ✭✭

    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.

    lfi9eqks.png
    t9vddxv9.png
    bwx6uhgb.png


    · Share on FacebookShare on Twitter
  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭
    https://community.smartsheet.com/discussion/comment/379267#Comment_379267

    @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

    · Share on FacebookShare on Twitter
  • MarnieMaria
    MarnieMaria ✭✭

    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

    · Share on FacebookShare on Twitter
  • 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.

    NEW - metrics sheet.png

    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

    · Share on FacebookShare on Twitter
  • 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

    · Share on FacebookShare on Twitter
  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭
    https://community.smartsheet.com/discussion/comment/379371#Comment_379371

    Happy to help - have a great day!

    Peggy

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!