Forum Discussion

IWT_GG's avatar
IWT_GG
Copper Contributor
Oct 25, 2019
Solved

please help me with ensuring DEFAULT DATE is entered in correct format in = IFNA(VLOOKUP(..), DATE..

...set-up or scenario

 

some text in the date fields are as correct in date format and some are inserted as TEXT format. These came from trying to insert a default date when a vlookup did not find suitable result in the lookup table.

 

=IFNA(VLOOKUP([@[uniq_line_item]],saved_promise_date_hard[#All],2,FALSE),"1/1/1900")

 

the thing is if the item being looked (uniq_line_item) exists in the look up table (saved_promise_date_hard) but there is no promised date ie blank then it returns the default value (1/1/1900) value in date format

 

but if the item being looked (uniq_line_item) DOES NOT EXIST in the look up table (saved_promise_date_hard) then default value (1/1/1900) returned is in text formatted of the default

 

My question would be how could I write this better that the default value entered is a date and not text. 

 

Thank you in advance

2 Replies