Unable to determine if a field contains a date less than today's date (involves vlookup).

ebcook07

Hi all,

I am trying to create a helper field to allow me to generate a report of records where the date from a field in my Smartsheet is less than today's date. This will be used to call attention that an update is needed.

The date field being used is a vlookup generated field from another sheet.

Formula: =VLOOKUP([Code]1, {Date_Table Range 14}, 12, false)

The field in the sheet is marked as a date field in both the current sheet and reference sheet.

When I try to use the formula listed below in my new helper field, I get an "#INVALID OPERATION" error:

=IF([Modified Target Completion Date]1 < TODAY(), true, false)

Any ideas?

Paul Newcome

Double check the source data is in fact being stored as a date.

ebcook07

Hi Paul,

Yes, the field that is being pulled in by VLOOKUP in the source sheet as well as the destination in the current sheet are both set as a date fields.

Just out of curiosity, I tried to +1 to the date field in the current sheet and instead of the new value being 03/19/2021, it returns 3/18/20221. I'm wondering if this is indicative of it being stored as a text field.

Paul Newcome

Right. So even though they are set to date fields it appears as if the data itself is not being stored as a date. Exactly how is that source data being populated?

ebcook07

Hi Paul,

It is being copy/pasted from an excel export.

Paul Newcome

Double check that the data in the excel file is a date type. That should get things working for you.