Avoid <error> during data cleansing

Follow

Anson Chan

• January 25, 2021 13:03

Hello, Good Morning.

I hit <error> cells whilst performing an operation that Involves
1. ASDATE 

2. SHIFTTIMEZONE

The <error> comes when the originated data column does not contain any String

I tried IF statement, but it gives me an argument error of mixing STRING with DATE.

The outcome I'd like to get to is - for any cells that doesn't have a DATE value, please leave it blank and do not perform the ASDATE or SHIFTTIMEZONE operation. I'd like an outcome that does not contain <error>.

Is this possible please? Thanks.

 

0

• 

  Konsta Danyliuk

  • January 25, 2021 13:07

  Hello Anson.
  It seems the formula you are using in the Attachment_AttachTime_Asia_DATE column can't handle empty values as the arguments.

  Do you think you could provide this formula from the Attachment_AttachTime_Asia_DATE column?

  0

  Comment actions Permalink
• 

  Anson Chan

  • January 26, 2021 01:11

  Hello Konsta, the formula I was using from Attachment_AttachTime_Asia_DATE was SHIFTTIMEZONE.

  Shifting time from UTC to Asia time

  thanks, Anson

  0

  Comment actions Permalink
• 

  Konsta Danyliuk

  • January 26, 2021 06:19

  Hello Anson.
  May I ask you to provide the whole formula, please. I need to see what columns it uses as arguments.

   

  0

  Comment actions Permalink
• 

  Anson Chan

  • January 26, 2021 06:31

  Hello Konsta,

  Please find the below

  SHIFTTIMEZONE(#Attachment_AttachTime_UTC_DATE;"Asia/Manila";"Etc/UTC")

  Thanks,

  Anson

  0

  Comment actions Permalink
• 

  Konsta Danyliuk

  • January 26, 2021 06:56

  Thank you, Anson.
  
  The SHIFTTIMEZONE function can't be applied to an empty value. This is the reason why you are getting <error> for those records where the #Attachment_AttachTime_UTC_DATE column is null. I guess if you hover over the errored cell, you will see the "argument of function SHIFTTIMEZONE is null" error message.

  Thereby you need to perform an emptiness check first and then apply SHIFTTIMEZONE to the records that have values only.

  For example, the following formula checks if the value in the #Attachment_AttachTime_UTC_DATE column is null and then returns the same empty value when true or applies SHIFTTIMEZONE when false.

  IF(ISNULL(#Attachment_AttachTime_UTC_DATE); #Attachment_AttachTime_UTC_DATE; SHIFTTIMEZONE(#Attachment_AttachTime_UTC_DATE;"Asia/Manila";"Etc/UTC"))

  I hope this helps.

  0

  Comment actions Permalink
• 

  Anson Chan

  • January 26, 2021 14:46

  Thank you Konsta, it worked! much appreciated for your guidance, always.

  Kind Regards, Anson

  0

  Comment actions Permalink
• 

  Konsta Danyliuk

  • January 26, 2021 15:35

  You are welcome, Anson.

  0

  Comment actions Permalink

Please sign in to leave a comment.