Forum Discussion
date formatting
I have a file with date looks like Jul 13, 2022, 5:00:00 PM and would like to change them to 7/13/2022 17:00 but format cell doesn't work. How do I do that?
2 Replies
Alex_Kim245 wrote: ``Jul 13, 2022, 5:00:00 PM``
If formatting the cell as Custom m/dd/yyyy hh:mm does not work, it is probably because the cell value is text, not a numeric dated.
Looks can be deceiving; and the format of the cell does not matter, as you know. Confirm with formulas of the form =ISTEXT(A1).
And the culprit could be just that pesky comma after the year, red-highlighted above.
The fix depends on information you have not provided.
One solution might be: select the range of text "dates", and use Find/Replace (ctrl+h) to change "2022," to "2022" (without quotes).
- you can put the date into a different cell and have that new cell force a format using formulas. say your date cell is A5, the formula in B5 would be =TEXT(A5,"mm/dd/yyyy hh:mm"). this should force the format without using the format in your Excel settings
