I use EXCEL 2011 on IMAC Under MAVERICKS. I seem to have a glitch in my Global F
ID: 3561640 • Letter: I
Question
I use EXCEL 2011 on IMAC Under MAVERICKS.
I seem to have a glitch in my Global Formatting of a simple worksheet.
Simple means all I am doing is entering numbers and dates, and the only formulae I apply are +, -, *, /, and SUM.
TO make my task easier, I want all numbers with 2 decimal places. So I thought I would achieve this by clicking the little box - TOP LEFT HAND CORNER -which highlights all the cells on this worksheet> FORMAT> Numbers> 2 Decimal Places > OK
So far so good.
However I also need to enter the dates in dd-mm-yy format which on a non-ofrmatted worksheet would be entered automatically in the correct legible format.
But NOT so on my global sheet - here as soon as I enter 12mar - I get a Number 47170.00 !
That was not the object of my exercise.
I would like the worksheeet to recognise the dates for what they are, and the numbers aree numbers with 2 decimal points.
How can I achieeve this?
Am I doing something wrong?
Thanks for all suggestions / advice!!!
Explanation / Answer
Dates in Excel are stored as sequential numbers, but the program has been designed to automatically recognize & format them based on the manner in which they're entered. See the introductory info in this Excel Help article for more detail: Date calculations in Excel
However, Excel only recognizes & formats Dates, Percentages, Currency, etc. when the cell is formatted as General. IOW, by pre-formatting all cells as Number you're implicitly telling the program to not format Date entries as anything other than their sequential numerical value.
Rather than altering the formatting, it may serve you better to use Excel> Preferences> Edit to set the preference for Display this number of decimal places. Be sure to read the Description @ the bottom of the preferences pane when you point to the preference, itself, in order to better understand how doing so affects data entry.
Another option may be to set up a workbook laid! out as you typically need! with the various ranges of cells formatted for the type of daata intended to be entered. The workbook can be saved as a template for generating new workbook files or can be set as a default workbook & default worksheet sheet. There isn't enough info about your needs & workflow to tell which/whether these options may be useful.