Excel Won't Format my Date

Copper Contributor

I have copied data from Excel running in compatibility mode and pasted it into the most current version of Excel where I need to the data to be. I need the data in this format: 01/01/2022 12:00:00 AM in order to perform my calculations. It's showing up as this: 01/01/2022 00. It will not let me change the format. I have 6 months of hourly data. Doing each one by hand will not be feasible. How can I get around this issue? Help! Please!

3 Replies
This is odd. Supposing that the actual cell contains a value - ie it is possible to add 1 to it and get a good result - the rest is just formatting. Mind, I get nervous when you write "I need the date in this format". The format should have no impact on downstream calculations. So perhaps your values ("01/01/2022 00") aren't numbers at all, but strings? Ouch - that means your data import has failed. That is more serious. But it is also curious because it does not sound like the original export was a string. (A string export would not be reformatted.) I do not understand what is going on. And if you are exporting numeric values, what difference can their formatting make on your downstream processing?



This might help you, you can add dates and time like this. internally Excel stores the date field as a double value type.



It sounds like those dates might be stored as text strings.  Pick any cell with a 'date' and open format cells.  Switch the category to General.  If General shows the date like this and not a 5 digital number, you have text.