Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date format when using combo box
I am using a combo box to fill in data in a spreadsheet.
One part of the data is the date. I have set the required cells to the following format dd/mm/yy, but when a date is input via the combo box sometimes the month and date are swapped around, eg 11/05/03 (11th May 03) is interpretted as 05/11/03 (5th Nov 03). other dates eg 23/06/03 are interpretted correctly. My Control Panel setting are set for the UK. Also, I want to only allow input in this box as a date format. if it is not recognised as a date eg 56/85/09 it will ask for another input. Any ideas? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date format when using combo box
Excel is US centric, so in most cases, if it can interpret a date as US
format, it will. Cdate will observer your regional settings, so you need to convert your date string to a date serial with Cdate Dim dtVal as Date Dim sStr as String dtVal = 0 Do sStr = InputBox("Enter Date") Loop Until isdate(sStr) or sStr = "" if sSTr = "" then exit sub dtVal = cDate(sStr) ' or Range("A1").Value = cDate(sStr) Range("A1").NumberFormat = "dd/mm/yyyy" -- Regards, Tom Ogilvy "Mikey May" wrote in message ... I am using a combo box to fill in data in a spreadsheet. One part of the data is the date. I have set the required cells to the following format dd/mm/yy, but when a date is input via the combo box sometimes the month and date are swapped around, eg 11/05/03 (11th May 03) is interpretted as 05/11/03 (5th Nov 03). other dates eg 23/06/03 are interpretted correctly. My Control Panel setting are set for the UK. Also, I want to only allow input in this box as a date format. if it is not recognised as a date eg 56/85/09 it will ask for another input. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
permanent conversion of 1904 date format to 1900 date format | Excel Worksheet Functions | |||
Format a combo box to show mmm-yyyy format | Excel Discussion (Misc queries) | |||
format combo box | Excel Discussion (Misc queries) | |||
I want my combo box to return a date format instead of a number? | Excel Discussion (Misc queries) | |||
keep date format in a Combo box | Excel Worksheet Functions |