Replace a value

Michele Adamski

Is it possible to delete only a particular value when it is found so that the cell is empty? For example, I pull a monthly list of all incidents created by a team from our ticketing system. If, at the time the report is run, there are any open incidents they come out with a "ClosedDate" of 12/30/1899 and "ClosedTime" of 00:00:00.

I'm certain this is coming from our ticketing software, but would like to delete any value in those two fields so that they are empty. 

Is this possible? I cannot find anything in the menu that seems to work so far. 

 

Mahmoud

Baba Majekodunmi_21731 said:

Hi Michele! Assuming you don’t want to filter those records out right? You just want to make those values blank?

Two options I can think of

 

1. Change Data Type, Replace, Change Data Type Back

I think that you need to change the type from date to string, and then use the Replace setting to replace 12/30/1899 to Blank. Then change the Data Type back to Date/Time. Those steps should work for the other column.

 

2. Conditional Formula Column

Alternatively you can create a new column with an If Statement: If([ClosedDate]={12/30/1899}, “ “, [ClosedDate]). I’m not 100% sure about this formula, as dates can be a bit picky, might have to adjust it a bit, maybe even change the date to string so this could work, but this would create a new column and you can hide the old column.

Hope this helps. Others might have another approach.

Hi Michele,

As Baba mentioned, option #1 would work if you convert the [Close Date] to character and then use the Replace option (fine and replace).

The formula in option #2 will not work.  Use the following formula instead:

if([Close Date]={12/30/1899},ctod(''),[Close Date],)

The reason is the true and false part of the If function must generate the same type, e.g. both characters, or numeric or date.  You need to use ctod function to convert character to date.

The [Close Time] is in character, so you can use replace (find/replace) and replace 00:00:00 with nothing.

Regards

Mo 

Baba_Majekodunmi_703

Hi Michele! Assuming you don’t want to filter those records out right? You just want to make those values blank?

Two options I can think of

 

1. Change Data Type, Replace, Change Data Type Back

I think that you need to change the type from date to string, and then use the Replace setting to replace 12/30/1899 to Blank. Then change the Data Type back to Date/Time. Those steps should work for the other column.

 

2. Conditional Formula Column

Alternatively you can create a new column with an If Statement: If([ClosedDate]={12/30/1899}, “ “, [ClosedDate]). I’m not 100% sure about this formula, as dates can be a bit picky, might have to adjust it a bit, maybe even change the date to string so this could work, but this would create a new column and you can hide the old column.

Hope this helps. Others might have another approach.

Mahmoud

Baba Majekodunmi_21731 said:

Hi Michele! Assuming you don’t want to filter those records out right? You just want to make those values blank?

Two options I can think of

 

1. Change Data Type, Replace, Change Data Type Back

I think that you need to change the type from date to string, and then use the Replace setting to replace 12/30/1899 to Blank. Then change the Data Type back to Date/Time. Those steps should work for the other column.

 

2. Conditional Formula Column

Alternatively you can create a new column with an If Statement: If([ClosedDate]={12/30/1899}, “ “, [ClosedDate]). I’m not 100% sure about this formula, as dates can be a bit picky, might have to adjust it a bit, maybe even change the date to string so this could work, but this would create a new column and you can hide the old column.

Hope this helps. Others might have another approach.

Hi Michele,

As Baba mentioned, option #1 would work if you convert the [Close Date] to character and then use the Replace option (fine and replace).

The formula in option #2 will not work.  Use the following formula instead:

if([Close Date]={12/30/1899},ctod(''),[Close Date],)

The reason is the true and false part of the If function must generate the same type, e.g. both characters, or numeric or date.  You need to use ctod function to convert character to date.

The [Close Time] is in character, so you can use replace (find/replace) and replace 00:00:00 with nothing.

Regards

Mo 

Michele Adamski

I didn't realize that I needed to change the data type on the date to text and then I would have more replace options. Prior to changing that, I had only ditto and null. 

Using variations of the IF SQL, always gave me this error, but I'm all set. 

 

 

Lesson learned:  I need to learn some serious SQL! 

 

Thanks, Baba and Mo!