Guests - If You want access to member only forums on FM. You will gain access only when you Sign-in or Sign-Up on Fishing Minnesota.

It's easy - LOOK UPPER right menu.

Sign in to follow this  
Followers 0
Whoaru99

MS Access / Excel experts?

5 posts in this topic

Normally I can figure out most of the stuff I need to do, but in this case I'm stumped.

I have several linked MS Access tables that I query and then export to Excel. Several of the columns that are exported are dates. In Excel I need to change the date format from mm/dd/yyyy to dd-mm-yyyy.

The problem I'm having is that for three of the four date columns, I can merely change the column/cell format and the date formats change, but on the fourth column, the only way I can get the date to change format is to change the column/cell format THEN click into a cell, hit "enter", and only then the date format changes.

What am I overlooking??

Share this post


Link to post
Share on other sites

Hmmm, I am not quite sure. It appears it should just work like the other 3 columns. Is the data highlighted when you change the format?

Did you try this? : Get the first cell in the correct format and then click and hold down the lower right corner of the first cell(when you get the black + sign) and just drag down to the bottom of your data. That should change the format of the other cells to the format of the first cell.

Good Luck

Share this post


Link to post
Share on other sites

Yeah, I just select/highlight the whole date columns and then change the cell formatting. Three of the four change no problem. Nothing seems to change the format of the dates in the fourth column until I select the cell, click the cursor into the cell, and then hit enter to exit the cell. As soon as I exit the cell using the enter key, the format changes.

Also, if it change the date value in any of cells that would not reformat, the new entered value takes on the desired format.

It's like somehow the format is locked on the data in that column.

I've tried changing to date serial number/value and back again, nothing seems to work...

Don't recall if I've tried to drag the format, but I'll give that a shot Monday morning.

Share this post


Link to post
Share on other sites

Another thing you could try is to insert a new blank column next to the 4th column and then copy and paste the 4th column into it. But instead of just pasting it, select "paste special" and then "values". This will get rid of anything that the format might be referencing. Then you could format the new column and delete the old one.

Share this post


Link to post
Share on other sites

I would compare the formats of the columns in Access and see if there is any difference. I am assuming that you are importing into a blank Excel spreadsheet if not check the format of the columns before you import the data. Also try the format painter. It is the icon with the paint brush.

Share this post


Link to post
Share on other sites
Guest
This topic is now closed to further replies.
Sign in to follow this  
Followers 0