| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Make date entered in an InputBox UK format
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Make date entered in an InputBox UK format
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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Make date entered in an InputBox UK format
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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Make date entered in an InputBox UK format
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" wrote in message ... 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" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Make date entered in an InputBox UK format
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" wrote in message ... 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Make Day CAPS In Custom Date Format | Excel Worksheet Functions | |||
| Excel 2002: How to make the date format uniform ? | Excel Discussion (Misc queries) | |||
| Condit Format? Make date change colors | Excel Worksheet Functions | |||
| How to make xy plot of date format data in both columns | Charts and Charting in Excel | |||
| how do i make a value entered be * 2 | New Users to Excel | |||
Hybrid Mode
