0

I have this sheet :

enter image description here

Formula A1 :

=text(today(), "DD MM YYYY")

Formula B1 :

=text(today(), "DD MMM YYYY")

Formula A4 (check if A1 = A2) :

=A1=A2

Formula B4 (check if B1 = B2) :

=B1=B2

I have a 'copy' button on the right and assign my custom function to copy from A1 to A2 and B1 to B2 :

function copy() {
  Sheet = SpreadsheetApp.getActiveSheet()

  var val = Sheet.getRange("A1").getValue()
  Sheet.getRange("A2").setValue(val)

  var val = Sheet.getRange("B1").getValue()
  Sheet.getRange("B2").setValue(val)  
}

The image above showing the result after I run the script. They look the exactly the same. but the B4 showing FALSE. What is wrong ?

I also tried to use 2 different names and change 'var' to 'const' :

function copy() {
  Sheet = SpreadsheetApp.getActiveSheet()
  const val = Sheet.getRange("A1").getValue()
  Sheet.getRange("A2").setValue(val)
  const val2 = Sheet.getRange("B1").getValue()
  Sheet.getRange("B2").setValue(val2)  
}
3
  • Have you tried using two different variable names?
    – Blindspots
    Jan 27 at 4:42
  • @Blindspots yes. but still the same. I also try to replace 'var' with 'const' with 2 different variable name, but no lucks.
    – andio
    Jan 27 at 4:43
  • I've just added the script using 2 variable names, tested with 'var' and 'const' as well , but still the same result.
    – andio
    Jan 27 at 4:51

1 Answer 1

1

The value in cell B2 is a numeric date, while the values in the other cells are text strings that just look like dates. A date is not equal to a text string even if they look the same. You can check the types of the four values with the isnumber() and istext() functions.

Google Sheets will automatically coerce text strings to numeric values when using Range.setValue() with a text string whose format matches one of the supported date, time, duration, currency or Boolean formats. In the example you give, 27 Jan 2024 is a valid date format, while 27 01 2024 is not. The former will be coerced to a numeric date and the latter will stay a text string.

To find whether two values look the same, use this pattern:

=to_text(B1) = to_text(B2)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.