Make date entered in an InputBox UK format

Hi,

I have a simple InputBox in excel which keys in a date into a single cell.
But when I key in 01/04/2006 the inputbox interprets the date to be 4th Jan
2006 rather than 1st April 2006.

is there anyway I can change this so it uses the English date format rather
than US?

Please help!
Thanks
Brian
Manchester,England

Dim dt as Date, s as String
s = Inputbox("enter date")

dt = cdate(s)

Range("b9").Value = dt

Cdate will interpret your string according to regional settings. It is
always best to convert to and work with dateserial numbers as soon possible.

--
Regards,
Tom Ogilvy


"Co-op Bank" wrote:

> Hi,
>
> I have a simple InputBox in excel which keys in a date into a single cell.
> But when I key in 01/04/2006 the inputbox interprets the date to be 4th Jan
> 2006 rather than 1st April 2006.
>
> is there anyway I can change this so it uses the English date format rather
> than US?
>
> Please help!
> Thanks
> Brian
> Manchester,England

Declare the variable as type date

Dim ans As Date
ans = InputBox("Please supply date")
Range("H5").Value = ans


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Co-op Bank" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I have a simple InputBox in excel which keys in a date into a single cell.
> But when I key in 01/04/2006 the inputbox interprets the date to be 4th
Jan
> 2006 rather than 1st April 2006.
>
> is there anyway I can change this so it uses the English date format
rather
> than US?
>
> Please help!
> Thanks
> Brian
> Manchester,England

Bob,
being in the UK, this doesn't cause the same problem for an ambiguous date
like

10/04/2006 (April 10, 2006)

--
Regards,
Tom Ogilvy


"Bob Phillips" wrote:

> Declare the variable as type date
>
> Dim ans As Date
> ans = InputBox("Please supply date")
> Range("H5").Value = ans
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Co-op Bank" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I have a simple InputBox in excel which keys in a date into a single cell.
> > But when I key in 01/04/2006 the inputbox interprets the date to be 4th
> Jan
> > 2006 rather than 1st April 2006.
> >
> > is there anyway I can change this so it uses the English date format
> rather
> > than US?
> >
> > Please help!
> > Thanks
> > Brian
> > Manchester,England
>
>
>

It does seem to resolve it Tom. I always used to CDate it as you suggested,
but I tried this one time, and it seems to work.

For instance, if I enter 05/04/2006, with a non-declared data type, stepping
through the code shows 05/04/2006 at all stages, and then changes it to
04/05/2006 (4th May here) when loading into a cell. Using a date type, it
stays as 05/04/2006 all the way thrugh.

Bob

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Bob,
> being in the UK, this doesn't cause the same problem for an ambiguous date
> like
>
> 10/04/2006 (April 10, 2006)
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Bob Phillips" wrote:
>
> > Declare the variable as type date
> >
> > Dim ans As Date
> > ans = InputBox("Please supply date")
> > Range("H5").Value = ans
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Co-op Bank" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi,
> > >
> > > I have a simple InputBox in excel which keys in a date into a single
cell.
> > > But when I key in 01/04/2006 the inputbox interprets the date to be
4th
> > Jan
> > > 2006 rather than 1st April 2006.
> > >
> > > is there anyway I can change this so it uses the English date format
> > rather
> > > than US?
> > >
> > > Please help!
> > > Thanks
> > > Brian
> > > Manchester,England
> >
> >
> >

That is good to know. Thanks.

--
Regards,
Tom Ogilvy



"Bob Phillips" wrote:

> It does seem to resolve it Tom. I always used to CDate it as you suggested,
> but I tried this one time, and it seems to work.
>
> For instance, if I enter 05/04/2006, with a non-declared data type, stepping
> through the code shows 05/04/2006 at all stages, and then changes it to
> 04/05/2006 (4th May here) when loading into a cell. Using a date type, it
> stays as 05/04/2006 all the way thrugh.
>
> Bob
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > Bob,
> > being in the UK, this doesn't cause the same problem for an ambiguous date
> > like
> >
> > 10/04/2006 (April 10, 2006)
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Bob Phillips" wrote:
> >
> > > Declare the variable as type date
> > >
> > > Dim ans As Date
> > > ans = InputBox("Please supply date")
> > > Range("H5").Value = ans
> > >
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "Co-op Bank" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi,
> > > >
> > > > I have a simple InputBox in excel which keys in a date into a single
> cell.
> > > > But when I key in 01/04/2006 the inputbox interprets the date to be
> 4th
> > > Jan
> > > > 2006 rather than 1st April 2006.
> > > >
> > > > is there anyway I can change this so it uses the English date format
> > > rather
> > > > than US?
> > > >
> > > > Please help!
> > > > Thanks
> > > > Brian
> > > > Manchester,England
> > >
> > >
> > >
>
>
>