Error calculating number of days between 2 dates

Kishan Naik RA

Hi all, I am having some issues running a seemingly simple formula to get the days between 2 dates in my sheet (screenshots below).

I first tried to just subtract the two date columns and received an INVALID OPERATION error code (I did try and add + "" even though all three are date columns but nothing changed).

=[Date on Waitlist (Mentor)]@row - [Date Applied (Mentor)]@row)

I also tried to use the NETDAYS function and got an INVALID DATA TYPE error code. The formula is below:

=NETDAYS([Date Applied (Mentor)]@row, [Date on Waitlist (Mentor)]@row)

None of this makes sense since all three columns are date columns. The 'Date Applied (Mentor)' and 'Date on Waitlist (Mentor)' columns are populated by VLOOKUPS but the columns that formula is reading are also date columns.

Any help would be appreciated!

Kelly Moore

Hey @Kishan Naik RA

Try changing your last column to a text/number field. The result will not be a date but a number (of days). Also, your formula will need to account for the dates fields that words in them instead of dates. Try your formula after changing the column type. Make sure you are testing this in a row where both fields are populated with an actual date.

=NETDAYS([Date Applied (Mentor)]@row, [Date on Waitlist (Mentor)]@row)

If you are still receiving an error after changing the column type then it is possible the lookup is not bringing the value in as a date.

Just as a quick test, in a text/number column, temporarily try this =IF(ISDATE(<insert either one of your date columns>@row), "true")

If smartsheet sees the value as a date, the formula above will return a 'true'. It will be blank if it is not a date. If this isn't a date, then we'll need to review your lookup.

Before trying the formula below, make sure your original formula works. The formula will ONLY work in a row with both dates populated. The IFERROR will mask errors so always build formulas that work first, then add the IFERROR to the formula once a working formula is verified.

=IFERROR(NETDAYS([Date Applied (Mentor)]@row, [Date on Waitlist (Mentor)]@row),"Not on Waitlist/App Sheet")

Does any of this work for you?

Kelly

Kishan Naik RA

Hey @Kelly Moore, thanks for the response. I still was receiving the same error after changing the column type so I ran =IF(ISDATE(<insert either one of your date columns>@row), "true"). I got a blank cell returned so your hunch was correct that something is wrong with my lookup.

I did check the sheets that the VLOOKUP is reading and all date columns are the right column type... I haven't ever had this happen to me so unclear on where to go next.

Kelly Moore

Hey @Kishan Naik RA

It could be that since your columns are a mixture of dates and text that smartsheet is becoming confused. To be very sure about your ISDATE formula, just for a test - overwrite the lookup formula and manually/directly enter a date in one of your lookup cells. Make sure the ISDATE check registers that value as true. Just as a double check.

You mentioned you have two sheets. In the screenshots above, are these shots of the same sheet? Is it possible to get shots of both sheets as well as your current lookup formula? I think you mentioned it was a vlookup. I'd like to test changing that to an Index/match (or maybe index/collect if we need further filtering).

Kishan Naik RA

I always forget about index/collect and index/match, I need to remember to go to those quicker in the future. I replaced my vlookup with an index/collect (since I have some criteria to filter which row to choose from the previous sheet) and all the formulas I was trying worked so it definitely was an issue with the vlookup. Below is a screenshot of the sheet that I had the vlookup formula reading previously. The 'Created Date' column is the following formula =DATEONLY(Created@row) reading the 'Created' column. I am guessing the vlookup wasn't working due to something with the formula but I haven't had this happen before so not really sure.

Kelly Moore

@Kishan Naik RA

In smartsheet, Index/match, Index/collect are preferred functions over vlookup for robustness and sheet performance. I consider the COLLECT function to be the most powerful function in the function toolbelt.

Shout out to me if you need assistance building your Index/Collect formulas for your sheet.

Let me know what you find either way. I'm a curious soul.

Kelly