NETDAYS returning #INVALID DATA TYPE

Meredith Fenwick

Hi -

I'm trying to calculate the number of days between two dates in a sheet. I have both of my columns that contain the start and end dates set up as DATE column type.

They both contain dates.

I use the formula "=NETDAYS([Hotel Check-In Date]@row, [Hotel Check-Out Date]@row)".

I have tried making the results column a DURATION type column as well as Text/Number and still get the error.

What am I doing wrong?

SmartWay360

Hi,

Is the fields you reference in the formula both date format?

Best,

Beata

Meredith Fenwick

Yes, they are both in mm/dd/yyyy format.

Scott Orsey

@Meredith Fenwick , That's really odd. The only thing I can think to do is what @SmartWay360 suggested and double check again the column type by Edit Column Properties for both of those columns. I realize this suggestion is likely frustrating... and I almost didn't post. I really don't know what else to try. Something is making the input values into the netdays() function into something other than dates. Maybe check to make sure the column names are spelled correctly in the formula. Maybe you have a similarly named column with a different data type.

Good luck as you keep troubleshooting. Be well.

Derek V

@Meredith Fenwick - Did you ever resolve this issue? I'm having the same problem. I think it's because one of my dates is generated by a formula. While the column is a date column, the data looks like a formula, not a date.

If this is the case, does anyone know how to create the NETDAYS formula so that it sees the date and not the formula?

Meredith Fenwick

Hi @Derek V - I ended up using a different formula because I had my days denoted one by one in a grid so was about do just do a countif off that instead of the netdays function. Both of the dates I was trying to pull the duration from were a product of an index/match formula so I think that must have been the source of the problem? Sorry I'm not more help.