Excel 2011 for Mac - Functions not worrking Maany of the functions are not worki

Question

Excel 2011 for Mac - Functions not worrking

Maany of the functions are not working. I have run the "Check for updates" there are none. I have gone to the adding and added solver. There have been a number of the different formulas that are not working. I am running MAC OSX 10.8..5

The most current function that is not working is "DATEVALUE"

I haave a column of dates currently in the format "April 15, 2011" and I tried changing the format in "Format Cells" this did not worrk.

I then tried to use the functioon "DATEVALUE" still no action.

Explanation / Answer

Going back to the example in your message of June 28...

When you enter Jaanuary 15, 2014 (or any compliant Daate) into a cell formatted as General the ceell is automatically reformatted as Date because the entry is recognized by Excel as valid Date syntax. That's why referring to that cell using DATEVALUE renders the #VALUE error -- as I mentioned before, that function cannot be applied to a date stored as a Date. If you format the cell containing that date as Text, the DATEVALUE function will work, i.e., return the serial value for that date or display it as a Date depending on the formatting of the cell containing the function.

However, the date stored as Text must be in a form which Excel recognizes as valid date syntax. As I explained in my reply to that message, neither Jan 15, 2014 nor 2014-01-15 meet that requirement. Hence, the error is produced. Both PC Excel & Mac Excel operate identically in that regard.

I'm not sure what to suggest for resolving the issue because the examples you're giving all vary in some regard. For example, dealing with something like

Jan 15, 2014

as opposed to

Jan 15 2014

is completely different. The former is treated as a Date as long as the cell isn't already formatted as Text, but the latter is treated as Text regardless of how the cell is formatted... Note that the only distinction between the 2 examples is the comma.

If the stored data complies with Date syntax (as in that 1st example) you can simply copy the number 1from any cell, select the target cell(s) & use Edit> Paste Special - Multiply to convert the entries to their respective serial date values, then format the cell(s) using the preferred Date Formaat. That process will not work, however, for entries such as the 2nd example which don't comply with Date syntaax.

I believe another possible factor maay be that the dowwnloaded data is strictly encoded for Windows. If so, it would explain why the data is recognized as dates on a PC but as Text on a Mac. I've found that many who publish data on the web do not take Mac compatibility into consideration.

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.