Forum Discussion
How to Remove Error in Date Field
If you are wanting to change all null values to a specific date (like 11/11/2011), then a simple replace values would work where you are replacing all null values with your ficticious date. You can also add a custom column with the following statement:
if [Your Date Column] = null then "11/11/2011" else [Your Date Column]
If you are trying to convert everything to a date and are receiving an error message, you can also try the following statement in a custom column (if your date column is currently text because of bad data):
try Date.FromText([Your Date Column]) otherwise (however you want the errors handled)
If you are wanting to change all null values to a specific date (like 11/11/2011), then a simple replace values would work where you are replacing all null values with your ficticious date. You can also add a custom column with the following statement:
if [Your Date Column] = null then "11/11/2011" else [Your Date Column]
If you are trying to convert everything to a date and are receiving an error message, you can also try the following statement in a custom column (if your date column is currently text because of bad data):
try Date.FromText([Your Date Column]) otherwise (however you want the errors handled)
Thanks Drew. For this case I was fine with the hard coded date setting and did successfully add it using the replace action to the query for that specific column.
As a follow up question I need the query to not error out on blank date entries. Blank date entries are valid as I'm looking at ticket history and am scaning tickets that are not yet 'closed'. I am going to use the closed ticket date field as a filter in order to display what tickets were closed during that period.
What's the best way to get around having a blank closed date value that is causing errors in my query for any record where the closed date is blank?
Thanks!
Could you send me an example of the error and the field causing the error. I am not sure I understand what you are trying to do with the field. If you are trying to write a formula to do something to a date but blank dates are throwing out errors, then use a try statement where you would write something like this:
try Date.FromText([Date]} otherwise (How you want to handle your error) This will basically try to do whatever you are wanting to do to the date field, and if it can't because it is blank, it will populate with whatever (null, "Text", etc.) But again, I might be misunderstanding your question. A screenshot would help if possible.
I want the column to be of type date/time. The empty entries in this case are marked as Error. I need a way to retain all of the information and cannot 'remove errors'. Besides replacing Error with a hardcoded date, what are my options?
