Re: Force excel to read dates as D/M/YYYY

Hi
I understand your difficulty.
One thing you could try, is to highlight the column of dates>data>text to columns>Next>Next>Date>M/D/Y>Finish

--
Regards
Roger Govier

"S Davis" <theseandavis@xxxxxxxxx> wrote in message news:f9d70891-8525-4de5-90f1-851921952b26@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I agree - however, this *** is being used in 6 different locations
and on more than double that for unique PC's. The conversion process
ends up being necessary as I can not convert PC's default regional
settings easily.

This is a temporary fix anyway until I can get everything converted,
paste it back in, and force M/D/YYYY date-entry from this point
forward - that seems to be the easiest solution, especially given that
various locations have been entering dates differently. Thanks for the
help :)

On May 5, 2:17 pm, "Roger Govier" <roger@technology4unospamdotcodotuk>
wrote:
Hi

Which version of Excel are you using?
Try changing the settings to United States, then back again to English.
With a Setting of English (United Kingdom) and location of United Kingdom,
dates will be interpreted correctly (well for me on all versions of XL from
97 through 2007)
If I change the settings to English (United States) and location United
States, then I get the behaviour you describe.

There should be no need to go through the conversion process you are
adopting.
--
Regards
Roger Govier

"S Davis" <theseanda...@xxxxxxxxx> wrote in message

news:c9984fda-004b-4a68-b528-4a5c43ccfc74@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

> On May 5, 10:49 am, "Roger Govier"
> <roger@technology4unospamdotcodotuk> wrote:
>> Hi

>> Control Panel>Regional Settings
>> Check what you have set up there, as this is where Excel takes it >> default
>> settings from.

>> --
>> Regards
>> Roger Govier

>> "S Davis" <theseanda...@xxxxxxxxx> wrote in message

>>news:f2f92417-da4d-4997-b6c9-96894c2bca97@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

>> > Hello everyone,

>> > I have a simple tracking *** that we have people entering overtime
>> > usage in to. I am trying to summarize this info with a basic
>> > pivotchart and group it by month, day, trade, etc.

>> > Anyway, there is a column for inputing the date in to. These dates
>> > have been entered as D/M/YYYY. The trouble is, Excel reads this as >> > M/D/
>> > YYYY, so where 11/4/2008 is intended to be interpreted as the 11th >> > of
>> > April, Excel is reading it as November the 4th.

>> > This results in an error whenever the "Day" exceeds 12, and so I can
>> > not group the results.

>> > Questions:
>> > 1) Is there a way to force excel to interpret a text input date in >> > the
>> > format I desire? Such as, "read 11/4/2008 as D/M/YYYY"?
>> > 2) If no to the above, I can probably parse the text to create the
>> > decimal value Excel uses for dates (where the 5th of May 2008 = >> > 39573
>> > for example), except I do not know how to arrive at 39573 from
>> > 5/5/2008. Any help here would also be appreciated.

>> > I'll keep working. Thanks in advance!

> Thanks. Default English settings means that a formula will be required
> to convert the data input already there.

> For those in the future needing help with this, here is my solution
> which works:

> =IF(ISERROR(DATE(YEAR(A6),MONTH(A6),DAY(A6))),DATE(RIGHT(TEXT(A6,0),
> 4),MID(A6,FIND("/",TEXT(A6,0))+1,(FIND("/",A6,FIND("/",TEXT(A6,0))+1)-
> FIND("/",TEXT(A6,0)))-1),LEFT(A6,
> (FIND("/",TEXT(A6,0))-1))),DATE(YEAR(A6),DAY(A6),MONTH(A6)))

> The inputted date resides in A6, and was input in the format D/M/YYYY,
> with the user manually typing in the slashes.

> Formula breakdown:
> IF(ISERROR(DATE(YEAR(A6),MONTH(A6),DAY(A6)))
> If: there's an error converting the input text to date (ie. if the
> "Day" the user input is read by excel as a month, it will error if the
> "Day" is over 12, eg. 13/4/2008 intended to be read as April 13th, but
> Errors since Excel assumes first number indicates the month number and
> there is no month 13)
> Then For Errors:

> DATE(RIGHT(TEXT(A6,0),4)
> Year: convert the input text to Text (to ensure nothing slips through)
> and strip the last 4 characters out for the year,

> MID(A6,FIND("/",TEXT(A6,0))+1,(FIND("/",A6,FIND("/",TEXT(A6,0))+1)-
> FIND("/",TEXT(A6,0)))-1)
> Month: Find the positions of the first and second "/" slashes and
> return the text between them

> LEFT(A6,(FIND("/",TEXT(A6,0))-1)))
> Day: Find the first "/" slash and return everything before it

> Else if no Errors in the original formula (ie. the "Day" is less than
> or equal to 12):

> DATE(YEAR(A6),DAY(A6),MONTH(A6)))
> Swap Months and Days: Return a Date formula where the Day and Month
> are swapped positions to match the input text.

> HTH
> -Sean

.