Subtracting Dates
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.
Answers
-
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!
-
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.
-
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!
-
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?
-
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!
-
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...
-
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!
-
Thank you! That was so obvious and I missed it! And it now works!
-
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!
-
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.
-
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!
-
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.
-
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!
-
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
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!