Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UK to US date format
I have a macro that reads;
- data from a form, and writes it to a central data sheet - and another macro that can retrieve the data and present it on the form again. In both macros the date is set as "dd-mmm-yy" format. However I am having a problem when there is a date such as 05-Jul-04. The 05 and the 07 of the day and month keep getting transposed so the date turns into 07-May-04 (!!!). Other dates (I guess ones where the day value is bigger than 12), seem fine. The network and applications at my work are set to UK date format, so why are these values changing over to US format? --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UK to US date format
Hi David.Wilson,
Other dates (I guess ones where the day value is bigger than 12), seem fine. The network and applications at my work are set to UK date format, so why are these values changing over to US format? Because VBA speaks American. To avoid the trouble you have, dim a variable as a Date and deliberately convert the string of the form's control to a date and put that into the Date variable: Sub test() Dim dDate As Date Dim sDate As String sDate = InputBox("Please enter a date")' a string dDate = CDate(sDate) 'Now convert string to Date, using regional settings ActiveCell.Value = dDate End Sub Regards, Jan Karel Pieterse Excel MVP www.jkp-ads.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UK to US date format
VBA is US Centric. If you work with the date serial number (the way dates
are stored) rather than the string representation of the date, you shouldn't have a problem. If you mean Userform when you say "form", then convert the string date in the text box to a date serial number using cDate. This should pay attention to your regional settings. Dim dtDate as Date dtDate = cDate(textbox1.text) as an example. -- Regards, Tom Ogilvy "David.Wilson " wrote in message ... I have a macro that reads; - data from a form, and writes it to a central data sheet - and another macro that can retrieve the data and present it on the form again. In both macros the date is set as "dd-mmm-yy" format. However I am having a problem when there is a date such as 05-Jul-04. The 05 and the 07 of the day and month keep getting transposed so the date turns into 07-May-04 (!!!). Other dates (I guess ones where the day value is bigger than 12), seem fine. The network and applications at my work are set to UK date format, so why are these values changing over to US format? --- Message posted from http://www.ExcelForum.com/ |
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 | |||
change date format dd/mm/yyyy to Julian date format? | Excel Worksheet Functions | |||
convert serial date format to normal date format | Excel Discussion (Misc queries) | |||
Excel 2000 date format cannot be set to Australian date format | Excel Discussion (Misc queries) | |||
Change a date in text format xx.xx.20xx to a recognised date format | Excel Programming |