Forum Discussion

B_Rucker's avatar
B_Rucker
Copper Contributor
Jul 18, 2022

Excel Won't Format my Date

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!

excel
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Jul 19, 2022

    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.

     

  • PS8888's avatar
    PS8888
    Copper Contributor
    Jul 19, 2022

    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.

  • ecovonrein's avatar
    ecovonrein
    Iron Contributor
    Jul 19, 2022
    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?

Resources