Refreshing Cell format

[hyde]

When I switch Cell format, for example date format from MM-DD-YY to mm/dd/yyyy sometimes I notice some cells remain in original format until I go to Cell, F2 and hit enter. (Or double click and hit enter).

Is there a way to force or refresh Cell format for all cells?


Sent from Z30 on Tapatalk

[FDibbins]

That could be because those cells were not real dates to begin with, and F2 (edit), converts them to dates. Test thosae that dont change, using =istext(cell-ref) FLASE indicates the cell contains text, not date

[hyde]

Thanks, I'll check that but I actually change all cells format first. Even if they were text, shouldn't that change them to display as date anyway?

If not, that's what I am trying to do, is there a bulk way to force them update? Instead of editing (when I edit, I don't make any changes, I just hit enter and it foxes the display format).


Sent from Z30 on Tapatalk

[FDibbins]

No, not necessarily. Formatting changes teh cosmetic appearance of a cell, it does not (generally) change teh contents of a cell

To edit an entire range of cells, highlight the range, scroll back to the top and click on the small yellow triangle you see on the left of the top cell. Select "Convert Text To Dates".

[hyde]

No, not necessarily. Formatting changes teh cosmetic appearance of a cell, it does not (generally) change teh contents of a cell

To edit an entire range of cells, highlight the range, scroll back to the top and click on the small yellow triangle you see on the left of the top cell. Select "Convert Text To Dates".

Thanks, but I don't see the triangle.
Here is my situation, I have an existing table with correctly formatted dates in a column, to make sure I select the column and apply Date as format in / / format.
Overtime, new dates are added, sometimes I paste them and when I paste them I can paste plain so they take the existing format but sometimes they don't they just remain in original date format until I select the whole column and apply Date format and then again go to Cell, edit and hit enter.


Sent from Z30 on Tapatalk

[FDibbins]

My mistake, it's a yellow diamond, not a triangle.

And like I said, just because you set the format on a cell, doesnt mean it will change the contents - if the contents of a cell is not an actual date (or a number that excel would see as a date), then you can format as date forever, it will not turn into a date.

If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[hyde]

My mistake, it's a yellow diamond, not a triangle.

And like I said, just because you set the format on a cell, doesnt mean it will change the contents - if the contents of a cell is not an actual date (or a number that excel would see as a date), then you can format as date forever, it will not turn into a date.

If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Thank you.
I see the diamond (with a tiny triangle at inside corner of the cell ) but those are not my issue.
Think of this situation.

I selected whole column A but hitting the top, selected Date as value.
I copied and pasted dates from another excel sheet (which may have them in a different format, but still dates) and I used paste plain text.
Now, dates are pasted, but they all have that little diamond.
To fix them, all I have to do is hit F2 then Enter. I don't have to edit anything in values or format or anything at all.

So, If I have 50 dates with same problem, and the cells are already assigned Date format, is there anyway to force refresh 50 cells at the same time instead of going through one by one and hitting F2 and Enter?

I do not click on the little diamond at all. F2+Enter fixes them automatically.

Also I noticed sometimes it just shows the Diamond with date question if it is 20xx or 19xx. Is there a default to set all 2 digit years to recognize as 20xx?

[FDibbins]

I do not click on the little diamond at all. F2+Enter fixes them automatically.

Also I noticed sometimes it just shows the Diamond with date question if it is 20xx or 19xx. Is there a default to set all 2 digit years to recognize as 20xx?

Can I suggest that you do click teh diamond? 1st, highlight the entire range, then scroll to the top (so the range stays selected), then click the diamond and follow the steps, select 20xx if your dates are 20??

[hyde]

Thanks for the info, I am using the diamond right now after pasting plain text, then in some cases I have to copy and paste special "formats" to fix this, however I see that Diamond comes back again, and this time asking me to select year indicator again xx/xx/20xx or xx/xx/19xx.
Is there anywhere in Excel I can specify that all dates be xx/xx/20xx?