In Excel:
The field length to be calculated has this value in cell C2: 01/09/2017 00:02:01
The formula used =LEN(C2)
The result returned is 15
The correct number of characters is 19
Format of cell C2 is a custom value: "yyyy"-"mm"-"dd" "hh":"mm":"ss"
When I use this in Google Sheets, the exact same steps, I get the correct value "19".
Please advise what I can correct in Excel, thank you!
>Solution :
In Excel, the LEN function counts all characters displayed in the cell, including those from the custom format code, but in Google Sheets, the LEN function only counts the underlying value, excluding formatting characters.
To have a portable result, convert result to TEXT first:
=LEN(TEXT(C2,"YYYY-MM-DD hh:mm:ss"))