Subtracting Dates

Pamb10
Pamb10 ✭✭✭✭
edited 05/14/21 in Formulas and Functions

I have 2 date columns in my sheet. When I try to use the NETDAYS function I get #InvalidDataType.

Does this function work with date column types?

What am I doing wrong?

Incidentally, the Created column has a formula to lookup the created date.


«1

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    How exactly are your dates populated? The NETDAYS function is designed to ONLY work with dates, so I am led to believe that your dates are not being stored as actual date values.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Pamb10
    Pamb10 ✭✭✭✭
    edited 05/14/21

    The Created date column is a Date column type, populated using the formula shown above. It does the vlookup to a sheet that contains the data also in a Date column. The data in that lookup sheet is either copy and pasted or imported.

    The Received date column is also a Date column type. I believe I did a copy/paste from excel.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. Let's run two quick tests...


    Insert a text/number column next to each of the date columns and enter the following as a column formula:

    =IF(ISDATE([Date Column]@row), "", "NOT A DATE")


    Then update the column name so that you can check both of the date type columns respectively. If you see any cells in either of these two columns that contain "NOT A DATE" then we will need to back track through there to figure out where the issue is originating from and how to fix it.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Pamb10
    Pamb10 ✭✭✭✭

    Ok, so the Created Date column reporting not a date.

    So I assume I need to convert the value returned from the vlookup to a date?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Yes. That can get a little tricky though. It may be easier to backtrack and convert the source data. How are you populating the data that is being pulled by the VLOOKUP?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Pamb10
    Pamb10 ✭✭✭✭
    edited 05/17/21

    I think i now have this worked out. Onto the next step which is averaging some data with conditions.

    This is the formula I have so far....

    =AVG(COLLECT({Days}, {Type}, "BH", {Date Recvd}, AND (@cell>=DATE(2021,01,01), @cell<DATE(2021,04,01))))

    it is giving #unparseable.


    PS. I just simplified the column names above as they are long...

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try removing the space after the AND function.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Pamb10
    Pamb10 ✭✭✭✭

    Thank you! That was so obvious and I missed it! And it now works!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Pamb10
    Pamb10 ✭✭✭✭

    Paul,

    I am wondering if you have any insight on the difference of the NETDAYS function in smartsheet vs subtracting dates in Excel.

    using the following data: Recvd Date 1/5/21, Created Date 1/6/21.

    Excel calculated a difference of 1.

    In SS, I got a calculation of 2.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It is because you have two dates entered. If you were to enter the same date as start and finish, then it would calculate as one day. Think of it more as duration than difference. If you wanted the difference, you can just subtract the dates.

    =[Finish Date]@row - [Start Date]@row



    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Pamb10
    Pamb10 ✭✭✭✭

    ok, I guess it makes sense when you think about it as a duration.

    I guess when I read about Netdays, Returns the number of days from a start date to an end date. I think difference, not duration.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I understand how you could read it like that. Here is a snippet from the article:

    The way I read this part though is that the start date is one day and the end date is one day. If you put both as the same date, then you will get one because you are only covering one day.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Hi, hope someone can help me with the following. It should be simple..

    I have a column with dates. Was imported from an Excel spreadsheet. Another column, is a date that I've imported via a vlookup. Should be simple, substract one date by the other...getting #invalid operation!

    Tried netdays, getting #invalid data type.

    Cleared the formula on the vlookup column, no change. Use the format painter, no change.

    Any ideas?

    Thanks

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @flevasseur71406

    Double check that both columns in Smartsheet are set as date type columns and make sure the column coming in from Excel is a date type column.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!