Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Format "stuck"
Sometimes - especially when I copy/paste from Access - dates look like dates,
I can format the cells as dates, but they're not *really* dates. For instance, if I format the cell as a number, I should get the serial date. Or, if I change the date format from mm/dd/yy to m/d/yyyy, it should change. If I manually delete a /, retype it and hit enter, it suddenly recognizes it as a date. But, if I try to automate this workaround with a find/replace, it still doesn't recognize it as a date. What's up? I *really* don't want to manually do it with a thousand cells. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Format "stuck"
Normally it will work with find and replace (find the slash and replace it
with the identical slash or find and replcae the 0 with a 0) so long as you reformt the cell to date prior to doing the replace. Failing that you can use the excel function Datevalue which takes a date in text and returns the Serial date... -- HTH... Jim Thomlinson "Undrline" wrote: Sometimes - especially when I copy/paste from Access - dates look like dates, I can format the cells as dates, but they're not *really* dates. For instance, if I format the cell as a number, I should get the serial date. Or, if I change the date format from mm/dd/yy to m/d/yyyy, it should change. If I manually delete a /, retype it and hit enter, it suddenly recognizes it as a date. But, if I try to automate this workaround with a find/replace, it still doesn't recognize it as a date. What's up? I *really* don't want to manually do it with a thousand cells. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Format "stuck"
So, I tried that before I saw your post. I had all the dates formatted as
number so I would see the change. I replaced all the slashes with an identical one. I didn't see any change . . . but, what had happened was it not only recognized it as a date, it changed all the cells to the appropriate format. The way I figured out that it was happening correctly was that the dates in my pivot table suddenly sorted correctly when I refreshed it. Thank you for the answer. "Jim Thomlinson" wrote: Normally it will work with find and replace (find the slash and replace it with the identical slash or find and replcae the 0 with a 0) so long as you reformt the cell to date prior to doing the replace. Failing that you can use the excel function Datevalue which takes a date in text and returns the Serial date... -- HTH... Jim Thomlinson "Undrline" wrote: Sometimes - especially when I copy/paste from Access - dates look like dates, I can format the cells as dates, but they're not *really* dates. For instance, if I format the cell as a number, I should get the serial date. Or, if I change the date format from mm/dd/yy to m/d/yyyy, it should change. If I manually delete a /, retype it and hit enter, it suddenly recognizes it as a date. But, if I try to automate this workaround with a find/replace, it still doesn't recognize it as a date. What's up? I *really* don't want to manually do it with a thousand cells. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Format "stuck"
When you change formatts of source data for a pivot table you need to refresh
the pivot table twice for the change to take effect. Just a quirk of pivot tables. That could have been the problem... -- HTH... Jim Thomlinson "Undrline" wrote: So, I tried that before I saw your post. I had all the dates formatted as number so I would see the change. I replaced all the slashes with an identical one. I didn't see any change . . . but, what had happened was it not only recognized it as a date, it changed all the cells to the appropriate format. The way I figured out that it was happening correctly was that the dates in my pivot table suddenly sorted correctly when I refreshed it. Thank you for the answer. "Jim Thomlinson" wrote: Normally it will work with find and replace (find the slash and replace it with the identical slash or find and replcae the 0 with a 0) so long as you reformt the cell to date prior to doing the replace. Failing that you can use the excel function Datevalue which takes a date in text and returns the Serial date... -- HTH... Jim Thomlinson "Undrline" wrote: Sometimes - especially when I copy/paste from Access - dates look like dates, I can format the cells as dates, but they're not *really* dates. For instance, if I format the cell as a number, I should get the serial date. Or, if I change the date format from mm/dd/yy to m/d/yyyy, it should change. If I manually delete a /, retype it and hit enter, it suddenly recognizes it as a date. But, if I try to automate this workaround with a find/replace, it still doesn't recognize it as a date. What's up? I *really* don't want to manually do it with a thousand cells. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mileage Claim Formula | New Users to Excel | |||
Cell date format doesnt change | Excel Worksheet Functions | |||
Change general format to US date format | Excel Discussion (Misc queries) | |||
Customized Date Format | Excel Worksheet Functions | |||
Compare dates (one cell not in date format) | Excel Discussion (Misc queries) |