Subtracting Dates

Pamb10

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.

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.

Pamb10

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

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.

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

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?

Pamb10

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

Try removing the space after the AND function.

Pamb10

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

Paul Newcome

Happy to help. 👍️

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

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

https://help.smartsheet.com/function/netdays

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

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.

flevasseur71406

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

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