date formatting

Copper Contributor

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
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

@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).