Forum Discussion
Solved
Changing date and time formatting to General converts to real number
It's a bit hard to describe the issue in the title. I've downloaded a 1000 row report from Microsoft Purview Audit in csv. When importing to Excel some dates are formatted as 'General' and other...
- i believe the problem is that the 1st dates showing as 'General' format are actually just TEXT that 'look like' a date while the cells with 'Custom' format are actual dates. you can try using Text to Columns to convert the text to DATE format or use an in cell formula like =DATEVALUE() to convert that text or if you are going to use Power Bi anyhow, you should be able to do the conversion in there.
i believe the problem is that the 1st dates showing as 'General' format are actually just TEXT that 'look like' a date while the cells with 'Custom' format are actual dates. you can try using Text to Columns to convert the text to DATE format or use an in cell formula like =DATEVALUE() to convert that text or if you are going to use Power Bi anyhow, you should be able to do the conversion in there.
Yes, you are correct that the general format is just text. I used your method to split text values of date and time then reformat everything using =DATEVALUE() and =TIMEVALUE() and added both together which was still a bit of a process because 12hour needed to be converted to 24 hour, but it worked. The reason for using Excel was also to change the regional formatting of the date field which I did not have access to the original file throwing up another challenge. All sorted now! Thanks for your help 🙂