Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
Microsoft Excel: Cell Formatting?
This is the issue. I was several dozen spreadsheets filled in with dates. I had to import them into another program in order to compile some information, but I was not able to successfully import unless I imported every field as a character field instead of a date field. After the compilation of the data and making some changes, I exported the data back to new excel spreadsheets.
The issue is all field came back in General format. So I basically changed all the date cells from General format to Date format. I then I applied some conditional formatting to all the cells, but realized the results were not showing up as expected. Basically what I found was this.
Just because you format the actual cells, does not automatically invoke the cell to show in the new format. I had to physically going to the cell itself and go to the end of the date and click enter. However, to have to go inside every date cell to click enter to invoke the format from general to date is a pain in the you know what. Is there anyway to get the formatting to take effect without me having to go into every cell to invoke the formatting?
3 Answers
- jmorgeLv 61 decade ago
actually the format does get invoked immediately. The issue here is due to the way the data was imported from your other system. I've run into this before as well and each instance is a little different (depending on how the data comes back from the other system). One thing I've done successfully is to copy the imported data then do a "paste special" and paste the "values" only into a different column. Then format that column as date and see if it works. If it does, you can copy/paste that new data back over your original data. Also try formatting the column of the new data to date format BEFORE you do the copy/paste special. Sometimes that works as well. Bottom line is, you may have to do some experimenting with copy/paste/paste special.
- Andrew LLv 71 decade ago
Say your data is in column A, in B1 put
=datevalue(a1)
... and drag this formula down col B. Now click in the column header of B, right-click, format cells, choose Date format.
You can now "fix" column B ... Copy -> Edit -> Paste special -> Values, then delete column A.
- 5 years ago
You could wrap your equation inside an if statement. Test the equation value, and use it if it's greater then 0 otherwise use 0. I.e. "IF(equation > 0, equation, 0)" For example to compute the sum of a block of cells you could use "IF(SUM(a1:b2) > 0, SUM(a1:b2), 0)". If the sum was greater then 0 then the sum will be used. But if some values were negative which caused the sum to be negative excel will put in 0 (or whatever you put in as the third argument to IF).
