Excel Won't Format my Date

[B_Rucker]

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!

[#2]

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?

[#3]

@B_Rucker 

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

[#4]

@B_Rucker 

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.