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 others are formatted as 'Custom'. They both appear to contain the same data when I click on the cells. But when I attempt to change the Custom formatted cells to General formatting, the date and time change to a decimal/float number.
General represents correctly.
Another type is imported as custom but represents correctly in the formula but not in the cell.
Converting Custom to General changes the date time formula to a float number.
I've tried splitting and reformatting as basic text then attempting to convert it back to a date time but I'm not having any luck, I just keep getting errors relating to the format.
End goal is to import into Power Bi. I get the same issues importing the csv into Power Bi.
Thanks.
Hey, I had the same issue before—forgot my Windows 10 password, tried everything I could think of, but nothing worked. Super frustrating! A friend suggested using iSeePasword Windows Password Recovery Pro to unlock it. It was pretty straightforward: just plug in a USB, follow the steps, and I could reset the password without touching any files. I was so relieved once it worked, no data loss or anything.
If you’re stuck like I was, give it a try—it really helped me out!
source: https://www.recodie.com/reset-forgotten-windows-password
2 Replies
- 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 🙂
