Aug 11 2019 01:27 AM - edited Aug 11 2019 01:28 AM
Aug 11 2019 01:27 AM - edited Aug 11 2019 01:28 AM
Hi everyone!
I have following problem which I don't know how to fix it: into a cell I introduce following text: 02:01. I save the file containing this cell, close and reopen it later. This time, instead of 02:01 appeared: 0,0840277777777778 or something like that! This happened with many similar cells! How to automatically restore the value: 02:01 and other similar values ?
Aug 11 2019 09:12 AM
Solution.@Deleted
Hi,
Excel read this "02:01" as a time value.
And when you format the cell that contains this value as General, it shows up as 0.0840277777777778.
The reason for this is that times in Excel are really decimal fractions.
So what's happens to your worksheet is that the cells that contain these time values are formatted somehow as General.
To switch it back, you need to format them as Time as the below screenshot:
Or you can customize the format to show up exactly as you want by pressing Ctrl+1, then go to Time and choose the format that you prefer, or go to Custom and in the Type box, type this format hh:mm or something similar.
Regards
Aug 11 2019 11:55 AM - edited Aug 11 2019 12:23 PM
Thank you for your kind support!
It didn"t help at all. I have followed all the mentioned steps but it didn't recover any text with the following form: ##-##. What else should be done?@Haytham Amairah
Aug 11 2019 12:15 PM - edited Aug 11 2019 12:15 PM
@Deleted
Could you provide the worksheet that you're working on or a sample of it to figure out the problem?
Aug 11 2019 12:21 PM
Sure, @Haytham Amairah
It constains just a few data because I have observed that many issues previous described appeared!
Aug 11 2019 12:48 PM
@Deleted
The problem is in the decimal symbol!
It's doesn't match the default the decimal symbol in the OS.
The default is the period (.) as the below screenshot:
FYI, Excel depends on the default number formats in the OS.
You can either change the default setting or replace the commas with periods in your worksheet using Find and Replace.
But it's recommended to leave the default setting as is.
After that, you can use the method I mentioned earlier.
But when use the find and replace, you may see the decimals show up like this: 1900-01-00
That's because the data are currently pre-formatted as Data.
Anyway, I've fixed them in the attached file.
Regards
Aug 11 2019 02:05 PM
Thank you very much. You have saved my day@Haytham Amairah
Aug 21 2019 09:45 AM
@Deleted Hello! You've posted your question in the Community Discussion space, which is intended for discussion around the Tech Community website itself, not product questions. I'm moving your question to the Excel space - please post Excel questions here in the future.
Aug 11 2019 09:12 AM
Solution.@Deleted
Hi,
Excel read this "02:01" as a time value.
And when you format the cell that contains this value as General, it shows up as 0.0840277777777778.
The reason for this is that times in Excel are really decimal fractions.
So what's happens to your worksheet is that the cells that contain these time values are formatted somehow as General.
To switch it back, you need to format them as Time as the below screenshot:
Or you can customize the format to show up exactly as you want by pressing Ctrl+1, then go to Time and choose the format that you prefer, or go to Custom and in the Type box, type this format hh:mm or something similar.
Regards