Office 365 Excel Formating problem

Question

I am having problems with cell formatting in Excel. I have Office 365 & I am running it on a Windows 10 PC.  I am trying to format the cell to accept anything that is typed in. The cell will contain alpha, numbers & special characters. When I format the cells in a particular column, one of the headings is numbers. I choose 'General' which is suppose to accept anything. I go to one of the cells in the formatted column and enter 'April 1966'. After I tab out of the cell or hit enter, the data becomes 'Apr-66'. I check the cell's format & now it shows up as 'Custom'. I then change the format to general & the 'Apr-66' now becomes '24198'. 

   The column is set up for a free format date. The cells contain a date for my Genealogy Family Tree on Ancestry.com. The format on Ancestry is 'DD MMM YYYY'. If don't now the day, I keep it blank. If I enter '01 Apr 1966' or 'XX Apr 1966' or '00 Apr 1966, in the cell, it is accepted.

   How do I format the cell to be free format. Also how do I prevent the formatting from changing from 'General' to 'Custom'. Also does anyone know what '24198' from above is suppose to represent.

                                   Thanks John

Answer 1

I would suggest there is nothing wrong with Excel and it doesn't need repairing - it is behaving as it always does.

What you have come up against is Excel's "helpful" date handling, that sometimes is but invariably isn't. Whenever you enter something that looks like a date in Excel (such as April 1966, 1/4/66 or similar), Excel tries to help by interpreting it as a date and formatting it accordingly. In the case of April 1966, it has changed it to 1st April 1966 (1/4/66). As far as I am aware there is no way to switch this feature off.

The 24198 is the number of days since 1/1/1900. Excel calculates dates as days, and part thereof, since 1/1/1900, thus 1/1/1900 is day 1, and 1/4/1966 is day 24198. Midday on that same day is 24198.5 and so forth.

I think the only way to genuinely be able to enter them as free format is to format the column as Text, or precede the entry with an apostrophe (') as you enter it to force it to be treated as text.

EDIT: PS - As an afterthought, if you're doing genealogy you're almost certainly going to encounter pre-1900 dates eventually, so formatting as text is really your only option without a lot of mucking around.

Answer 2

Hello,

We can see here that you are having issues with cell formatting. If the formatting is set to general, it shouldn't be changed to any formats. It's either you selected a format on your present workbook or there is something wrong within Excel itself. Let us perform the troubleshooting steps below and check if it helps:

Step 1: Repair an Office application to refresh Excel.

          a. Right-click the Start button (lower-left corner), and select Programs and Features on the pop-up menu.

          b. Right-click the Microsoft Office product you want to repair, and select Change.

          c. From the How would you like to repair your Office Programs screen, select Online Repair to make sure everything gets fixed, and then select Repair. The Quick Repair option is also available, which runs faster but only detects and then replaces corrupted files.

          d. Follow the instructions on the screen to complete.

Step 2: Check if there is an available update for Excel application. Please check this **link**on how to install the update.

Let us know if there's any changes after performing these steps.

Thank you.