Solved Can't change date format properly

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

agent_washingtub

Thread Starter
Joined
Feb 5, 2015
Messages
121
I have a column of dates in dd/mm/yyyy format, yet nothing I do lets me sort from oldest to newest.
Basically it doesn't recognize the column as having only dates, therefore only allows me to sore from A-Z.
I have tried what was posted on a few other forums, such as copy-pasting the column in notebook and
then back to excel. I've also tried the text to column wizard, with no help.
I've tried creating entirely new excel files and copy-pasting the dates there, either directly
or copying to notepad and then to the new spreadsheet. Nothing seems to work.
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,442
they are not formatted as dates and probably text

can you attach here ?

click on the date cell and change the format to number or general
you should see a number like
42293
which is today 16th Oct 2015
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,442
as suspected they are not dates
if you put in cell B

=Value(a1)
and copy down

that will change the text into a date value
like 42293
and then format as a date
and it should now work
 

Attachments

agent_washingtub

Thread Starter
Joined
Feb 5, 2015
Messages
121
Ok, that did successfully change column B into a date column that can sort properly.
But how do I take just that column and put it back into my main spreadsheet?
I have columns A through O with information(I can't post that file online).
Is there a way to isolate just the B column with the correct dates, or does it have
to stay next to the A column with the other dates?
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,442
how is the data populated originally
Is this something that changes - or a one off

is column A the only date thats the issue

you could do a copy
paste special value from B to A
 

agent_washingtub

Thread Starter
Joined
Feb 5, 2015
Messages
121
Alright, so copying the B column to my original spreadsheet was not working, but copying
the rest of the columns in my original file into the one I posted here seems to work.
However, I didn't notice the first time, but using =Value(a1) in B and copy down did NOT work. For the first few
dates it did, but once it reaches the 15th of January, most of the cells in B are #Value!

Also I'm unclear as to your question, "how is data populated originally"
No, nothing in the column changes, or is one off. When I copy\paste it next to my other columns
the rows are lined up properly.
And yes, only column A is an issue, it is also the only column with dates.
 

agent_washingtub

Thread Starter
Joined
Feb 5, 2015
Messages
121
I figured out part of the problem. The code =Value(a1) works up into the date 12/01/2015
The value it give is for December 1st, instead of January 12.
Even when I format a cell BEFORE writing any data in it, it changes it.
Example: I format a cell to date dd/mm/yyyy (Canadian English), then
I type 02/01/2015 (January 2nd) then when I press enter the date changes
itself to 01/02/2015 automatically.
It's as if regardless of what format I choose, it assumes I'm entering the dates as mm/dd/yyyy
I'm truly lost
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,442
the date will be what ever the PC is setup for
under control panel >

so if you have english dates on the PC
it will only know english dates
so when you enter
1/1/15
if english on the PC
it will assume thats 1st Jan 2015

now that will be change for the number (number of days since 1st Jan 1900 - i think thats the default setting and so
that number is
42005

lets take
3rd Jan
3/1/15
thats = 42007

Now when you change that to Canadian
1/3/15
as a format that will work - as it is still using 42007 - 3rd Jan and just changing the format
BUT

if you enter

1/3/15

the PC does not know that is canadian and because the PC is in english will make that the
1st March 2015
and that number is
42064

does that help at all ???

----------------
the reason value may not be working is that the date may not be text through out
---------------
Also I'm unclear as to your question, "how is data populated originally"
who put the data into the spreadsheet - and how is it put in
is it imported from some other system ?
 

DaveBurnett

Account Closed
Joined
Nov 11, 2002
Messages
12,970
Dates are always going to be a tricky subject in any international situation and I think that the authors of Excel have covered the subject pretty well in allowing you to specify a huge range of formats including defining your own. I think the only thing that you really are restricted in is the way that it is held internally and that is days since 1900 (can be negative). All other dates you see are just DISPLAY formats.
 

agent_washingtub

Thread Starter
Joined
Feb 5, 2015
Messages
121
Just to be clear, the problem as you described is correct, just in reverse.
Typing 1/3/15 for January 3rd is what I want, but right now it is received as March 1st.

And yes you are right, my computers date is displayed in m/d/y format, which is what
excel changes it to.

And you are also correct in suggesting that it is not in text format all the way through.
I'm not sure why it isn't, as the first spreadsheet I entered in same manner didn't
have this problem.
The dates were all manually entered by me, typing them in by hand.

Anyway, I will be leaving the office soon and will try once more Monday morning.
It the problem still persists, I will just have to suck it up and re-enter the data manually.
My sincerest thank you for all of your help, you've been very clear and patient.
 

DaveBurnett

Account Closed
Joined
Nov 11, 2002
Messages
12,970
In the same way that you format the date in an output, you can also specify the format that an input is assumed to be.
Don't forget that without your supplied format it will default to the default in your (the computer's) region.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top