Force excel to read dates as D/M/YYYY

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

S Davis - 05 May 2008 18:44 GMT

Hello everyone,

I have a simple tracking sheet 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!

Reply to this Message

Roger Govier - 05 May 2008 18:49 GMT

Hi

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

Signature

Regards
Roger Govier

> Hello everyone,
>
[quoted text clipped - 19 lines]
>
> I'll keep working. Thanks in advance!

Reply to this Message

S Davis - 05 May 2008 20:12 GMT

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

> Hi
>
[quoted text clipped - 29 lines]
>
> > 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

Reply to this Message

Roger Govier - 05 May 2008 22:17 GMT

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.

Signature

Regards
Roger Govier

> On May 5, 10:49 am, "Roger Govier"
> <roger@technology4unospamdotcodotuk> wrote:
[quoted text clipped - 76 lines]
> HTH
> -Sean

Reply to this Message

S Davis - 06 May 2008 17:52 GMT

I agree - however, this sheet 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
>
[quoted text clipped - 96 lines]
> > HTH
> > -Sean

Reply to this Message

Roger Govier - 07 May 2008 00:23 GMT

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

Signature

Regards
Roger Govier

> I agree - however, this sheet is being used in 6 different locations
> and on more than double that for unique PC's. The conversion process
[quoted text clipped - 116 lines]
>> > HTH
>> > -Sean

Reply to this Message

S Davis - 12 May 2008 19:00 GMT

On May 6, 4:23 pm, "Roger Govier" <roger@technology4unospamdotcodotuk>
wrote:

> Hi
> I understand your difficulty.
[quoted text clipped - 129 lines]
> >> > HTH
> >> > -Sean

Thanks for the help. This is much easier than a formula for
conversion, and I can run this as a macro on an entire column quite
easily when I need to analyse the data. Thanks!

Reply to this Message