SOLVED
Home

Error cell excel

%3CLINGO-SUB%20id%3D%22lingo-sub-798856%22%20slang%3D%22en-US%22%3EError%20cell%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-798856%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone!%3C%2FP%3E%3CP%3E%26nbsp%3BI%20have%20following%20problem%20which%20I%20don't%20know%20how%20to%20fix%20it%3A%20into%20a%20cell%20I%20introduce%20following%20text%3A%2002%3A01.%20I%20save%20the%20file%20containing%20this%20cell%2C%20close%20and%20reopen%20it%20later.%20This%20time%2C%20instead%20of%26nbsp%3B02%3A01%20appeared%3A%26nbsp%3B0%2C0840277777777778%20or%20something%20like%20that!%20This%20happened%20with%20many%20similar%20cells!%20How%20to%20automatically%26nbsp%3Brestore%20the%20value%3A%26nbsp%3B02%3A01%20and%20other%20similar%20values%20%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-799081%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20cell%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-799081%22%20slang%3D%22en-US%22%3E%3CP%3E.%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373548%22%20target%3D%22_blank%22%3E%40ionutzmarion%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20read%20this%20%2202%3A01%22%20as%20a%20time%20value.%3C%2FP%3E%3CP%3EAnd%20when%20you%20format%20the%20cell%20that%20contains%20this%20value%20as%20%3CSTRONG%3EGeneral%3C%2FSTRONG%3E%2C%20it%20shows%20up%20as%200.0840277777777778.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EThe%20reason%20for%20this%20is%20that%20times%20in%20Excel%20are%20really%20decimal%20fractions.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20what's%20happens%20to%20your%20worksheet%20is%20that%20the%20cells%20that%20contain%20these%20time%20values%20are%20formatted%20somehow%20as%20General.%3C%2FP%3E%3CP%3ETo%20switch%20it%20back%2C%20you%20need%20to%20format%20them%20as%20%3CSTRONG%3ETime%3C%2FSTRONG%3Eas%20the%20below%20screenshot%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F126542i8B7F2D302DEE5882%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Time%20Format.png%22%20title%3D%22Time%20Format.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20you%20can%20customize%20the%20format%20to%20show%20up%20exactly%20as%20you%20want%20by%20pressing%20%3CSTRONG%3ECtrl%2B1%3C%2FSTRONG%3E%2C%20then%20go%20to%20%3CSTRONG%3ETime%3C%2FSTRONG%3Eand%20choose%20the%20format%20that%20you%20prefer%2C%20or%20go%20to%20%3CSTRONG%3ECustom%3C%2FSTRONG%3Eand%20in%20the%20Type%20box%2C%20type%20this%20format%20%3CSTRONG%3Ehh%3Amm%3C%2FSTRONG%3Eor%20something%20similar.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-799209%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20cell%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-799209%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373548%22%20target%3D%22_blank%22%3E%40ionutzmarion%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20provide%20the%20worksheet%20that%20you're%20working%20on%20or%20a%20sample%20of%20it%20to%20figure%20out%20the%20problem%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-799211%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20cell%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-799211%22%20slang%3D%22en-US%22%3E%3CP%3ESure%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20constains%20just%20a%20few%20data%20because%20I%20have%20observed%20that%20many%20issues%20previous%20described%20appeared!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-799201%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20cell%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-799201%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20your%20kind%20support!%3C%2FP%3E%3CP%3EIt%20didn%22t%20help%20at%20all.%20I%20have%20followed%20all%20the%20mentioned%20steps%20but%20it%20didn't%20recover%20any%20text%20with%20the%20following%20form%3A%20%23%23-%23%23.%26nbsp%3B%20What%20else%20should%20be%20done%3F%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-799213%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20cell%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-799213%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373548%22%20target%3D%22_blank%22%3E%40ionutzmarion%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%20in%20the%20decimal%20symbol!%3C%2FP%3E%3CP%3EIt's%20doesn't%20match%20the%20default%20the%20decimal%20symbol%20in%20the%20OS.%3C%2FP%3E%3CP%3EThe%20default%20is%20the%20period%20(.)%20as%20the%20below%20screenshot%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20658px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F126557iC982E3851D77CEFD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Decimal%20Symbol.png%22%20title%3D%22Decimal%20Symbol.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFYI%2C%20Excel%20depends%20on%20the%20default%20number%20formats%20in%20the%20OS.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20either%20change%20the%20default%20setting%20or%20replace%20the%20commas%20with%20periods%20in%20your%20worksheet%20using%20Find%20and%20Replace.%3C%2FP%3E%3CP%3EBut%20it's%20recommended%20to%20leave%20the%20default%20setting%20as%20is.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20that%2C%20you%20can%20use%20the%20method%20I%20mentioned%20earlier.%3C%2FP%3E%3CP%3EBut%20when%20use%20the%20find%20and%20replace%2C%20you%20may%20see%20the%20decimals%20show%20up%20like%20this%3A%201900-01-00%3C%2FP%3E%3CP%3EThat's%20because%20the%20data%20are%20currently%20pre-formatted%20as%20Data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyway%2C%20I've%20fixed%20them%20in%20the%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-799254%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20cell%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-799254%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20very%20much.%20You%20have%20saved%20my%20day%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-815557%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20cell%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-815557%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373548%22%20target%3D%22_blank%22%3E%40ionutzmarion%3C%2FA%3E%26nbsp%3BHello!%20You've%20posted%20your%20question%20in%20the%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FCommunity-Discussion%2Fbd-p%2FCommunityQuestions%22%20target%3D%22_blank%22%3ECommunity%20Discussion%20space%3C%2FA%3E%2C%20which%20is%20intended%20for%20discussion%20around%20the%20Tech%20Community%20website%20itself%2C%20not%20product%20questions.%20I'm%20moving%20your%20question%20to%20the%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fbd-p%2FExcelGeneral%22%20target%3D%22_blank%22%3EExcel%20space%3C%2FA%3E%20-%20please%20post%20Excel%20questions%20here%20in%20the%20future.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Deleted
Not applicable

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 ? 

7 Replies
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:

Time Format.png

 

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

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 

@Deleted

 

Could you provide the worksheet that you're working on or a sample of it to figure out the problem?

Sure, @Haytham Amairah 

 

It constains just a few data because I have observed that many issues previous described appeared!

Highlighted

@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:

Decimal Symbol.png

 

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

Thank you very much. You have saved my day@Haytham Amairah 

@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. 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
48 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies