I've got a spreadsheet in which, up to now, one column has the following
format for the date/time (in a single column): Nov-24-05 06:51:49 PST.
(It's a spreadsheet of EBay charges, previously copied and pasted from my
EBay 'Account Status' page). I've slipped up and failed to download the data
for ages, so for all of last year and part of 2005, I'll have to copy and
paste the data in from the invoices instead, but these have the following
format in that column: Nov 26 06:08:05. Of course, every time I paste this
data in, Excel treats it (in this particular example) as the 1st of November
2026. I'd be really grrateful for some suggestions on the quickest way to
change the format of the incoming data so that it's compatible with the data
in the existing spreadsheet...

If you put the year you want to use in B1, then use the formula
=DATE(B$1,MONTH(A1),YEAR(A1)-2000)+MOD(A1,1)
--
David Biddulph

"browniebodrum" wrote in message
...
I've got a spreadsheet in which, up to now, one column has the following
format for the date/time (in a single column): Nov-24-05 06:51:49 PST.
(It's a spreadsheet of EBay charges, previously copied and pasted from my
EBay 'Account Status' page). I've slipped up and failed to download the
data
for ages, so for all of last year and part of 2005, I'll have to copy and
paste the data in from the invoices instead, but these have the following
format in that column: Nov 26 06:08:05. Of course, every time I paste
this
data in, Excel treats it (in this particular example) as the 1st of
November
2026. I'd be really grrateful for some suggestions on the quickest way to
change the format of the incoming data so that it's compatible with the
data
in the existing spreadsheet...

Great, thanks.

"David Biddulph" wrote:

If you put the year you want to use in B1, then use the formula
=DATE(B$1,MONTH(A1),YEAR(A1)-2000)+MOD(A1,1)
--
David Biddulph

"browniebodrum" wrote in message
...
I've got a spreadsheet in which, up to now, one column has the following
format for the date/time (in a single column): Nov-24-05 06:51:49 PST.
(It's a spreadsheet of EBay charges, previously copied and pasted from my
EBay 'Account Status' page). I've slipped up and failed to download the
data
for ages, so for all of last year and part of 2005, I'll have to copy and
paste the data in from the invoices instead, but these have the following
format in that column: Nov 26 06:08:05. Of course, every time I paste
this
data in, Excel treats it (in this particular example) as the 1st of
November
2026. I'd be really grrateful for some suggestions on the quickest way to
change the format of the incoming data so that it's compatible with the
data
in the existing spreadsheet...

David - The cells that I've now re-formatted using your suggested formula now
don't sort properly with the original ones. I think it's because the
'original' cells have 'PST' at the end, whereas the 'introduced/reformatted'
cells don't. Any ideas how I can get round that? I tried using 'concatenate'
to add a space then 'PST' to the new cells, once they had the correct format
for the date, but doing this messed up the date format again. I don't really
want to strip out the 'PST' bit from all the others, you never know when
you're going to need an element of data once you've deleted it... Thanks
for your help.

"David Biddulph" wrote:

If you put the year you want to use in B1, then use the formula
=DATE(B$1,MONTH(A1),YEAR(A1)-2000)+MOD(A1,1)
--
David Biddulph

"browniebodrum" wrote in message
...
I've got a spreadsheet in which, up to now, one column has the following
format for the date/time (in a single column): Nov-24-05 06:51:49 PST.
(It's a spreadsheet of EBay charges, previously copied and pasted from my
EBay 'Account Status' page). I've slipped up and failed to download the
data
for ages, so for all of last year and part of 2005, I'll have to copy and
paste the data in from the invoices instead, but these have the following
format in that column: Nov 26 06:08:05. Of course, every time I paste
this
data in, Excel treats it (in this particular example) as the 1st of
November
2026. I'd be really grrateful for some suggestions on the quickest way to
change the format of the incoming data so that it's compatible with the
data
in the existing spreadsheet...

You could either format the cells with custom format
mmm-dd-yy hh:mm:ss" PST"
or use the formula (modified as a result of your other post)
=TEXT(DATE(B$1,MONTH(A1),MOD(YEAR(A1),100))+MOD(A1 ,1),"mmm-dd-yy hh:mm:ss""
PST""")
The former solution would leave the cells as date/ time so they can have
further arithmetic performed on the resuls if necessary. The latter would
leave the cells as text, which might be the format of your earlier data if
you wanted to perform consistent operations on both.
--
David Biddulph

"browniebodrum" wrote in message
...
David - The cells that I've now re-formatted using your suggested formula
now
don't sort properly with the original ones. I think it's because the
'original' cells have 'PST' at the end, whereas the
'introduced/reformatted'
cells don't. Any ideas how I can get round that? I tried using
'concatenate'
to add a space then 'PST' to the new cells, once they had the correct
format
for the date, but doing this messed up the date format again. I don't
really
want to strip out the 'PST' bit from all the others, you never know when
you're going to need an element of data once you've deleted it... Thanks
for your help.

"David Biddulph" wrote:

If you put the year you want to use in B1, then use the formula
=DATE(B$1,MONTH(A1),YEAR(A1)-2000)+MOD(A1,1)
--
David Biddulph

"browniebodrum" wrote in
message
...
I've got a spreadsheet in which, up to now, one column has the
following
format for the date/time (in a single column): Nov-24-05 06:51:49 PST.
(It's a spreadsheet of EBay charges, previously copied and pasted from
my
EBay 'Account Status' page). I've slipped up and failed to download
the
data
for ages, so for all of last year and part of 2005, I'll have to copy
and
paste the data in from the invoices instead, but these have the
following
format in that column: Nov 26 06:08:05. Of course, every time I paste
this
data in, Excel treats it (in this particular example) as the 1st of
November
2026. I'd be really grrateful for some suggestions on the quickest way
to
change the format of the incoming data so that it's compatible with the
data
in the existing spreadsheet...

Hi again David

I've now found that it doesn't seem to work for all dates...

Mar 31 09:01:21 in the original file becomes 01/03/1931 09:01:21 when it's
pasted into Excel, but when I apply your formula using 2006 as the required
year, it becomes 21/12/2005 09:01:21. Can you help me further? Am I doing
something wrong?

"David Biddulph" wrote:

If you put the year you want to use in B1, then use the formula
=DATE(B$1,MONTH(A1),YEAR(A1)-2000)+MOD(A1,1)
--
David Biddulph

"browniebodrum" wrote in message
...
I've got a spreadsheet in which, up to now, one column has the following
format for the date/time (in a single column): Nov-24-05 06:51:49 PST.
(It's a spreadsheet of EBay charges, previously copied and pasted from my
EBay 'Account Status' page). I've slipped up and failed to download the
data
for ages, so for all of last year and part of 2005, I'll have to copy and
paste the data in from the invoices instead, but these have the following
format in that column: Nov 26 06:08:05. Of course, every time I paste
this
data in, Excel treats it (in this particular example) as the 1st of
November
2026. I'd be really grrateful for some suggestions on the quickest way to
change the format of the incoming data so that it's compatible with the
data
in the existing spreadsheet...

Now I realise why that's going wrong. The default in Windows Regional
Options is to treat a 2 digit date as between 1930 and 2029.
Try
=DATE(B$1,MONTH(A1),MOD(YEAR(A1),100))+MOD(A1,1)
--
David Biddulph

"browniebodrum" wrote in message
...
Hi again David

I've now found that it doesn't seem to work for all dates...

Mar 31 09:01:21 in the original file becomes 01/03/1931 09:01:21 when
it's
pasted into Excel, but when I apply your formula using 2006 as the
required
year, it becomes 21/12/2005 09:01:21. Can you help me further? Am I
doing
something wrong?

"David Biddulph" wrote:

If you put the year you want to use in B1, then use the formula
=DATE(B$1,MONTH(A1),YEAR(A1)-2000)+MOD(A1,1)
--
David Biddulph

"browniebodrum" wrote in
message
...
I've got a spreadsheet in which, up to now, one column has the
following
format for the date/time (in a single column): Nov-24-05 06:51:49 PST.
(It's a spreadsheet of EBay charges, previously copied and pasted from
my
EBay 'Account Status' page). I've slipped up and failed to download
the
data
for ages, so for all of last year and part of 2005, I'll have to copy
and
paste the data in from the invoices instead, but these have the
following
format in that column: Nov 26 06:08:05. Of course, every time I paste
this
data in, Excel treats it (in this particular example) as the 1st of
November
2026. I'd be really grrateful for some suggestions on the quickest way
to
change the format of the incoming data so that it's compatible with the
data
in the existing spreadsheet...

If you format A1 as Text and paste in:

Nov 26 06:08:05

Then
=DATE(2000+RIGHT(A1,2),MONTH(1*A1),DAY(1*A1))
will display
11/26/2005
or any other date format you apply.
--
Gary's Student
gsnu200706


"browniebodrum" wrote:

I've got a spreadsheet in which, up to now, one column has the following
format for the date/time (in a single column): Nov-24-05 06:51:49 PST.
(It's a spreadsheet of EBay charges, previously copied and pasted from my
EBay 'Account Status' page). I've slipped up and failed to download the data
for ages, so for all of last year and part of 2005, I'll have to copy and
paste the data in from the invoices instead, but these have the following
format in that column: Nov 26 06:08:05. Of course, every time I paste this
data in, Excel treats it (in this particular example) as the 1st of November
2026. I'd be really grrateful for some suggestions on the quickest way to
change the format of the incoming data so that it's compatible with the data
in the existing spreadsheet...

I've copied that formula into B1, is that correct? Maybe not, because it
doesn't return 11/26/2005, it returns 01/11/2005. It's corrected the year
but not the day of the month. How would I change it for other years?

"Gary''s Student" wrote:

If you format A1 as Text and paste in:

Nov 26 06:08:05

Then
=DATE(2000+RIGHT(A1,2),MONTH(1*A1),DAY(1*A1))
will display
11/26/2005
or any other date format you apply.
--
Gary's Student
gsnu200706


"browniebodrum" wrote:

I've got a spreadsheet in which, up to now, one column has the following
format for the date/time (in a single column): Nov-24-05 06:51:49 PST.
(It's a spreadsheet of EBay charges, previously copied and pasted from my
EBay 'Account Status' page). I've slipped up and failed to download the data
for ages, so for all of last year and part of 2005, I'll have to copy and
paste the data in from the invoices instead, but these have the following
format in that column: Nov 26 06:08:05. Of course, every time I paste this
data in, Excel treats it (in this particular example) as the 1st of November
2026. I'd be really grrateful for some suggestions on the quickest way to
change the format of the incoming data so that it's compatible with the data
in the existing spreadsheet...

I can't reproduce what you are seeing:

On my computer, A1 has: Nov 26 06:08:05 as Text
B1 has: =DATE(2000+RIGHT(A1,2),MONTH(1*A1),DAY(1*A1))
B1 displays: 11/26/2005
formatted as mm/dd/yyy

--
Gary's Student
gsnu200706


"browniebodrum" wrote:

I've copied that formula into B1, is that correct? Maybe not, because it
doesn't return 11/26/2005, it returns 01/11/2005. It's corrected the year
but not the day of the month. How would I change it for other years?

"Gary''s Student" wrote:

If you format A1 as Text and paste in:

Nov 26 06:08:05

Then
=DATE(2000+RIGHT(A1,2),MONTH(1*A1),DAY(1*A1))
will display
11/26/2005
or any other date format you apply.
--
Gary's Student
gsnu200706


"browniebodrum" wrote:

I've got a spreadsheet in which, up to now, one column has the following
format for the date/time (in a single column): Nov-24-05 06:51:49 PST.
(It's a spreadsheet of EBay charges, previously copied and pasted from my
EBay 'Account Status' page). I've slipped up and failed to download the data
for ages, so for all of last year and part of 2005, I'll have to copy and
paste the data in from the invoices instead, but these have the following
format in that column: Nov 26 06:08:05. Of course, every time I paste this
data in, Excel treats it (in this particular example) as the 1st of November
2026. I'd be really grrateful for some suggestions on the quickest way to
change the format of the incoming data so that it's compatible with the data
in the existing spreadsheet...

Sorry, but I can't reproduce what you are seeing, either. I've checked and
re-checked the entries, started from scratch and done it all again, but I've
still got 11/01/2005 resulting from the formula in B1. I wish I could show
you a screendump to prove it!

"Gary''s Student" wrote:

I can't reproduce what you are seeing:

On my computer, A1 has: Nov 26 06:08:05 as Text
B1 has: =DATE(2000+RIGHT(A1,2),MONTH(1*A1),DAY(1*A1))
B1 displays: 11/26/2005
formatted as mm/dd/yyy

--
Gary's Student
gsnu200706


"browniebodrum" wrote:

I've copied that formula into B1, is that correct? Maybe not, because it
doesn't return 11/26/2005, it returns 01/11/2005. It's corrected the year
but not the day of the month. How would I change it for other years?

"Gary''s Student" wrote:

If you format A1 as Text and paste in:

Nov 26 06:08:05

Then
=DATE(2000+RIGHT(A1,2),MONTH(1*A1),DAY(1*A1))
will display
11/26/2005
or any other date format you apply.
--
Gary's Student
gsnu200706


"browniebodrum" wrote:

I've got a spreadsheet in which, up to now, one column has the following
format for the date/time (in a single column): Nov-24-05 06:51:49 PST.
(It's a spreadsheet of EBay charges, previously copied and pasted from my
EBay 'Account Status' page). I've slipped up and failed to download the data
for ages, so for all of last year and part of 2005, I'll have to copy and
paste the data in from the invoices instead, but these have the following
format in that column: Nov 26 06:08:05. Of course, every time I paste this
data in, Excel treats it (in this particular example) as the 1st of November
2026. I'd be really grrateful for some suggestions on the quickest way to
change the format of the incoming data so that it's compatible with the data
in the existing spreadsheet...

The only way I can reproduce what you are seeing is to use:
=DATE(2000+RIGHT(A1,2),MONTH(1*A1),DAY(1))
instead of
=DATE(2000+RIGHT(A1,2),MONTH(1*A1),DAY(1*A1))

Perhaps copy/paste right from my posting.... However, do not fret over it.
Just use David's formula.
--
Gary's Student
gsnu200706


"browniebodrum" wrote:

Sorry, but I can't reproduce what you are seeing, either. I've checked and
re-checked the entries, started from scratch and done it all again, but I've
still got 11/01/2005 resulting from the formula in B1. I wish I could show
you a screendump to prove it!

"Gary''s Student" wrote:

I can't reproduce what you are seeing:

On my computer, A1 has: Nov 26 06:08:05 as Text
B1 has: =DATE(2000+RIGHT(A1,2),MONTH(1*A1),DAY(1*A1))
B1 displays: 11/26/2005
formatted as mm/dd/yyy

--
Gary's Student
gsnu200706


"browniebodrum" wrote:

I've copied that formula into B1, is that correct? Maybe not, because it
doesn't return 11/26/2005, it returns 01/11/2005. It's corrected the year
but not the day of the month. How would I change it for other years?

"Gary''s Student" wrote:

If you format A1 as Text and paste in:

Nov 26 06:08:05

Then
=DATE(2000+RIGHT(A1,2),MONTH(1*A1),DAY(1*A1))
will display
11/26/2005
or any other date format you apply.
--
Gary's Student
gsnu200706


"browniebodrum" wrote:

I've got a spreadsheet in which, up to now, one column has the following
format for the date/time (in a single column): Nov-24-05 06:51:49 PST.
(It's a spreadsheet of EBay charges, previously copied and pasted from my
EBay 'Account Status' page). I've slipped up and failed to download the data
for ages, so for all of last year and part of 2005, I'll have to copy and
paste the data in from the invoices instead, but these have the following
format in that column: Nov 26 06:08:05. Of course, every time I paste this
data in, Excel treats it (in this particular example) as the 1st of November
2026. I'd be really grrateful for some suggestions on the quickest way to
change the format of the incoming data so that it's compatible with the data
in the existing spreadsheet...