Formula issues

%3CLINGO-SUB%20id%3D%22lingo-sub-1610022%22%20slang%3D%22en-US%22%3EFormula%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1610022%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20team%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20facing%20a%20problem%20that%20formula%20always%20ran%20out%2C%20anyone%20knows%20how%20to%20solve%3F%3C%2FP%3E%3CP%3EYou%20may%20see%20below%20picture%20that%20showing%20%23%23%23%23%23%2C%20actually%20the%20original%20format%20is%20general%2C%20but%20it%20changed%20automatically%20after%20I%20open%20it%20few%20days%20later.%20Sometime%20it%20changed%20to%20currency%2C%20or%20even%20date%20etc%2C%20but%20not%20the%20original%20format%20-%20general..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22pearltho_0-1598343652986.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F214601iC0053A02D117FFC4%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22pearltho_0-1598343652986.png%22%20alt%3D%22pearltho_0-1598343652986.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1610116%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1610116%22%20slang%3D%22en-US%22%3Emake%20the%20cells%20wider%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1610117%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1610117%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F770076%22%20target%3D%22_blank%22%3E%40pearltho%3C%2FA%3E%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20column%20width%20issue%2C%20please%20expand%20the%20width%20%26amp%3B%20if%20the%20issue%20still%20the%20same%20then%20request%20you%20attached%20the%20sample%20file.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%2FBest%20Answer%26nbsp%3Bto%20help%20the%20other%20members%20find%20it%20mor%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1612484%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1612484%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F746148%22%20target%3D%22_blank%22%3E%40ramizassaf%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20the%20format%20itself%20already%20changed%20to%20date%20automatically..%20my%20original%20format%20should%20be%20general%2C%20the%20data%20should%20be%20just%20number%2C%20eg.%20100%2C%20200...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethis%20is%20what%20it%20shows%20if%20make%20the%20cells%20wider.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22pearltho_0-1598407068133.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F214734iA3A789C348A3DE85%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22pearltho_0-1598407068133.png%22%20alt%3D%22pearltho_0-1598407068133.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1612519%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1612519%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F156456%22%20target%3D%22_blank%22%3E%40Faraz%20Shaikh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20it%20doesn't%20matter%20with%20the%20cells%20width%2C%20after%20make%20it%20wider%2C%20it%20still%20same.%3C%2FP%3E%3CP%3Ethe%20original%20format%20should%20be%20'general'%20and%20showing%20number%2C%20etc%20100%2C%205%2C%20200....%3C%2FP%3E%3CP%3Ebut%20it%20change%20to%20date%20automatically.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehere%20u%20go%20for%20the%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1612586%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1612586%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F770076%22%20target%3D%22_blank%22%3E%40pearltho%3C%2FA%3E%26nbsp%3BAll%20your%20numbers%20have%20a%20custom%20format%20%22%5B%24-en-MY%2C1%5Dd%2Fm%2Fyyyy%3B%40%22%2C%20turning%20them%20into%20dates.%20All%20cells%20with%20positive%20numbers%20will%20be%20displayed%20as%20date%2C%20starting%20with%20number%201%20as%20January%201%2C%201900.%20Widen%20the%20cells%20and%20these%20positive%20numbers%20will%20show%20dates.%20Negatives%20numbers%2C%20however%2C%20can%20not%20be%20displayed%20as%20dates%20and%20will%20always%20result%20in%20%23%23%23%23%23%23%23%23%23%2C%20regardless%20the%20width%20of%20the%20cell.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20since%20you%20don't%20want%20dates%2C%20select%20all%20cells%20in%20a%20sheet%2C%20format%20as%20%22General%22.%20Then%20select%20the%20columns%20that%20should%20be%20dates%20(columns%20A%20mostly)%20and%20format%20these%20as%20%22Date%22%20and%20all%20your%20numbers%20and%20dates%20will%20display%20as%20expected.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-08-26%20at%2005.38.33.png%22%20style%3D%22width%3A%20361px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F214738i7B427B91E6664479%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Screenshot%202020-08-26%20at%2005.38.33.png%22%20alt%3D%22Screenshot%202020-08-26%20at%2005.38.33.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1612635%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1612635%22%20slang%3D%22en-US%22%3E%3CP%3EMany%20thanks%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20able%20to%20change%20it%20back%20to%20the%20format%20I%20want%2C%20the%20problem%20is%2C%20after%20few%20days%2C%20when%20I%20reopen%20the%20same%20file%2C%20the%20format%20will%20change%20again%2C%20that's%20why%20need%20your%20expert%20experience%20to%20share%20me%20how%20to%20avoid%20it%20change%20automatically.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi team,

 

I facing a problem that formula always ran out, anyone knows how to solve?

You may see below picture that showing #####, actually the original format is general, but it changed automatically after I open it few days later. Sometime it changed to currency, or even date etc, but not the original format - general..

 

pearltho_0-1598343652986.png

 

6 Replies
Highlighted
make the cells wider

Hi @pearltho

 

It is column width issue, please expand the width & if the issue still the same then request you attached the sample file. 

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it mor

Highlighted

@Ramiz_Assaf 

 

Hi, the format itself already changed to date automatically.. my original format should be general, the data should be just number, eg. 100, 200...

 

 

this is what it shows if make the cells wider.

 

pearltho_0-1598407068133.png

 

Highlighted

@Faraz Shaikh 

 

Hi, it doesn't matter with the cells width, after make it wider, it still same.

the original format should be 'general' and showing number, etc 100, 5, 200....

but it change to date automatically.

 

here u go for the file.

Highlighted

@pearltho All your numbers have a custom format "[$-en-MY,1]d/m/yyyy;@", turning them into dates. All cells with positive numbers will be displayed as date, starting with number 1 as January 1, 1900. Widen the cells and these positive numbers will show dates. Negatives numbers, however, can not be displayed as dates and will always result in #########, regardless the width of the cell. 

 

But since you don't want dates, select all cells in a sheet, format as "General". Then select the columns that should be dates (columns A mostly) and format these as "Date" and all your numbers and dates will display as expected.

Screenshot 2020-08-26 at 05.38.33.png

Highlighted

Many thanks@Riny_van_Eekelen 

I able to change it back to the format I want, the problem is, after few days, when I reopen the same file, the format will change again, that's why need your expert experience to share me how to avoid it change automatically.