Jul 18 2020 04:32 AM
Jul 18 2020 06:53 AM - edited Jul 19 2020 07:14 AM
Suppose your date is in cell A1, use this:
As you can see, this simply takes the first 10 characters from your current timestamp string and Excel properly recognizes it as a date, on which you can use Date-based functions and date arithmetic:
Some useful feedback from @Sergei Baklan regarding the above recommendation.
While Excel acting on the above formula and arithmetic will recognize the result as a date, it is not foolproof, so wrap the LEFT function with DATEVALUE to force the result into the correct underlying format. Then format the cell as a date.
I prefer DATEVALUE to the -- operator as what's happening is more clear to readers.
Jul 18 2020 01:49 PM
This formula returns text, not date. If, for example, check
it returns TRUE since any text is always "more" than any number. Another story is that use such text in arithmetic operations or some formulas Excel automatically transforms text to number. Same as ="10"+1 returns 11.
More reliable will be to push such conversion as =--LEFT(A1,10) and apply date format to the result.
If we have column with datetimezone texts we may apply to it Date->Text to Columns using T as separator and remove second column after that.