Datevlaue function/ day/month/year function

[Nav]

Hello

My PC is set up as UK region and hence displays dates as 20/01/06. However
I have a spreadsheet sent from the US formatted as general and reflects
01/20/06 as the date. I am trying to use a vlookup where the dates match, I
have tried format cells, date dd-mm-yy but this does not work it still shows
01/20/06.

I have also tried to use datevalue, day, month, year functions (as a serial
number) and then looking up those values. However they all return #value.
Does anyone have any ideas how I can convert this date to UK date format?

Any help is appreciated.

Thanks.

Hi

Are the 'US dates' actual Excel dates? If you type
=A2+1
(assuming the 'date' is in A2) does it give you 01/21/06?
You could try selecting the column, go to Data/Text to columns and use the
wizard. On the third page you can select (in the top right corner) that it
is a date - and its format.
Make sure you backup your data before you start.

Hope this helps.
Andy.

"Nav" <[email protected]> wrote in message
news:[email protected]...
> Hello
>
> My PC is set up as UK region and hence displays dates as 20/01/06.
> However
> I have a spreadsheet sent from the US formatted as general and reflects
> 01/20/06 as the date. I am trying to use a vlookup where the dates match,
> I
> have tried format cells, date dd-mm-yy but this does not work it still
> shows
> 01/20/06.
>
> I have also tried to use datevalue, day, month, year functions (as a
> serial
> number) and then looking up those values. However they all return #value.
> Does anyone have any ideas how I can convert this date to UK date format?
>
> Any help is appreciated.
>
> Thanks.

[Nav]

Hello Andy

Thanks for your suggestions, but is does not appear to work.

If I type =a2+1 then it still returns the #value. (if the day is more than
12) eg.01/20/05 = #value.

However if the date is 1/12/05 then it returns 2nd Dec 05, whereas it should
be 13th Jan 05.

I also tried the Data/Text to col but it still has no effect. Is there
anything further I can try.

Thanks in advance.

"Andy" wrote:

> Hi
>
> Are the 'US dates' actual Excel dates? If you type
> =A2+1
> (assuming the 'date' is in A2) does it give you 01/21/06?
> You could try selecting the column, go to Data/Text to columns and use the
> wizard. On the third page you can select (in the top right corner) that it
> is a date - and its format.
> Make sure you backup your data before you start.
>
> Hope this helps.
> Andy.
>
> "Nav" <[email protected]> wrote in message
> news:[email protected]...
> > Hello
> >
> > My PC is set up as UK region and hence displays dates as 20/01/06.
> > However
> > I have a spreadsheet sent from the US formatted as general and reflects
> > 01/20/06 as the date. I am trying to use a vlookup where the dates match,
> > I
> > have tried format cells, date dd-mm-yy but this does not work it still
> > shows
> > 01/20/06.
> >
> > I have also tried to use datevalue, day, month, year functions (as a
> > serial
> > number) and then looking up those values. However they all return #value.
> > Does anyone have any ideas how I can convert this date to UK date format?
> >
> > Any help is appreciated.
> >
> > Thanks.
>
>
>

[[email protected]]

Nav
It sounds like there is a mix of dates
Excel is interpreting those dates it can as standard UK dates the others
it is leaving as text.

I belive Andy's suggestion of using the Text to Columns will work if you
follow the following steps.

Backup the file before you start
Select the original column of dates
Data\Text to Columns
Step1: delimited
Step2: no delimiters selected
Step3: Column Data Format as Date type MDY
Finish

This should convert all of the dates into Excel dates in the form you
need.

Please give it a go and let us know how you get on

hth RES

[Nav]

Hi Robert

Thanks for your help, but it still doesn't work. I have taken the file onto
3 different PC's and tried it to no avail. I think I will have to try and
get help writing a macro for this.

Regards, Nav

"[email protected]" wrote:

> Nav
> It sounds like there is a mix of dates
> Excel is interpreting those dates it can as standard UK dates the others
> it is leaving as text.
>
> I belive Andy's suggestion of using the Text to Columns will work if you
> follow the following steps.
>
> Backup the file before you start
> Select the original column of dates
> Data\Text to Columns
> Step1: delimited
> Step2: no delimiters selected
> Step3: Column Data Format as Date type MDY
> Finish
>
> This should convert all of the dates into Excel dates in the form you
> need.
>
> Please give it a go and let us know how you get on
>
> hth RES
>

[daddylonglegs]

You could try a formula in another column, e.g. if your dates are in column A use this in B1 and copy down

=IF(ISTEXT(A1),REPLACE(MID(A1,4,3)&A1,7,3,"")+0,DATE(YEAR(A1),DAY(A1),MONTH(A1)))

If the "date" is of the form 01/20/05 and excel is treating it as text it will convert to a date, i.e. 20/01/05 or if it's being treated as a date but the wrong one - e.g. 10th december becomes 12th October - it will switch the month and day.

Note: this is designed to work where the days and months are always shown as two digits e.g. 01 not just 1

[daddylonglegs]

...another way would be to change your regional settings to US - import the file and then change your settings back

[Nav]

Thanks for your help, this works a real treat.

Regards, Nav

"daddylonglegs" wrote:

>
> You could try a formula in another column, e.g. if your dates are in
> column A use this in B1 and copy down
>
> =IF(ISTEXT(A1),VALUE(REPLACE(MID(A1,4,3)&A1,7,3,"")),DATE(YEAR(A1),DAY(A1),MONTH(A1)))
>
> If the "date" is of the form 01/20/05 and excel is treating it as text
> it will convert to a date, i.e. 20/01/05 or if it's being treated as a
> date but the wrong one - e.g. 10th december becomes 12th October - it
> will switch the month and day.
>
> Note: this is designed to work where the days and months are always
> shown as two digits e.g. 01 not just 1
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=503297
>
>