14,970
edits
Data cleaning: Difference between revisions
Jump to navigation
Jump to search
→check if field value was not fulfilled: NULL, empty value
| Line 187: | Line 187: | ||
#* solution: step1: Replace the year > 100 from this year with empty value at EXCEL: {{kbd | key =<nowiki>=IF(ISERR(YEAR(A2)), "", IF(YEAR(A2)<1914, "", A2))</nowiki>}} (this formula also handle empty value and non well-formatted column value ex: 0000-12-31 ) ; step2: change the format of cell to time format | #* solution: step1: Replace the year > 100 from this year with empty value at EXCEL: {{kbd | key =<nowiki>=IF(ISERR(YEAR(A2)), "", IF(YEAR(A2)<1914, "", A2))</nowiki>}} (this formula also handle empty value and non well-formatted column value ex: 0000-12-31 ) ; step2: change the format of cell to time format | ||
#* trivial approach : EXCEL: {{kbd | key =<nowiki>=IF(ISERR(YEAR(A2)), "", IF(YEAR(A2)-YEAR(NOW())>100, "", A2))</nowiki>}} {{exclaim}} this formula could not handle empty value because it return 0. If I change the format of cell to time format, 0 will become 1900/1/0. | #* trivial approach : EXCEL: {{kbd | key =<nowiki>=IF(ISERR(YEAR(A2)), "", IF(YEAR(A2)-YEAR(NOW())>100, "", A2))</nowiki>}} {{exclaim}} this formula could not handle empty value because it return 0. If I change the format of cell to time format, 0 will become 1900/1/0. | ||
# Using PHP [http://php.net/manual/en/function.empty.php empty()] function to find 0, null, false, empty string, empty array values. | # Using PHP | ||
#* [http://php.net/manual/en/function.empty.php empty()] function to find 0, null, false, empty string, empty array values. | |||
#* <code>if(empty($var) && $var !== 0 && $var !== "0"){ .. } </code> to find null, false, empty string, empty array values BUT not 0. | |||