| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 40015 | 2003-11-25 09:21:00 | Access and Excel short date format | tbacon_nz (865) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 194534 | 2003-11-25 09:21:00 | Running Access 2000 and Excel 2000 on WinXP Home. It appears that both these apps are assuming US format MM/DD/YY for the short date format, even though as far as I can tell I have all my regional settings set to New Zealand. Formatting a cell as date in Excel only gives me mm/dd/yy as option for short date. Using Date functions in Access displays as mm/dd/yy. Any ideas? P.S. I've just confirmed that our other PC running the same setup does the same thing. |
tbacon_nz (865) | ||
| 194535 | 2003-11-25 10:07:00 | Under the Regional and Languages option in Control Panel, check under the Regional Options - Customise button. You can specify the short date format there. In Excel, if you look in Format - Cells, select Customise and create your own dd/mm/yy or whatever you need. |
godfather (25) | ||
| 194536 | 2003-11-25 18:24:00 | My point is that the short date format in the regional settings is already dd/mm/yy, but I am not getting that by default in Excel and Access. I realize I can set whatever format I want within the app, but I shouldn't need to if I am happy with the default. Hope that makes sense! |
tbacon_nz (865) | ||
| 194537 | 2003-11-25 18:37:00 | when you format the cell, go to custom and look for it there | wotz (335) | ||
| 194538 | 2003-11-25 18:45:00 | Hi, in VBA code both Access and Excel use the US format for date functions regardless of regional settings. It may be the case for Access expressions as well. Use the format function to change the format.... Variable = Format(MyDate,"DD/MM/YYYY") hth |
parry (27) | ||
| 194539 | 2003-11-25 19:08:00 | Further to my post and assuming your writing code, see this link here (users.bigpond.net.au) which discusses some VBA traps including the US format for date strings. hth |
parry (27) | ||
| 194540 | 2003-11-25 19:15:00 | Aaah - all is explained. It was in VBA in Access that I first noticed the issue. I got round it through the sort of code you provided. Thanks for the info. | tbacon_nz (865) | ||
| 194541 | 2003-11-25 19:28:00 | My pleasure. I was cursing over this when I first struck the problem so I can understand the frustration. It doesnt matter when your subtracting or adding of course but when it comes to displaying it rears its ugly head :-) | parry (27) | ||
| 194542 | 2003-11-25 19:42:00 | I still don't get your issue. I have Access, and Excel, and the short date format for both has always been UK format (dd/mm/yyyy). Perhaps it's because in my installation of Windows (using an unattended setup), I painfully went through every single possible option, customising the setup to how I needed it, but all the same... | agent (30) | ||
| 194543 | 2003-11-25 21:33:00 | Hi agent its a bit confusing and has nothing to do with your setup. Its easier to explain using an example, so download this workbook here (www.geocities.com). Make sure you say yes to enabling macros. I assure you I havent written anything dodgy there :-) The workbook has two drop down boxes. There are two control buttons - one adds items to the drop down boxes and the other clears the items from the drop down boxes. Enter a date in cell a1 then click the "Add Date in A1 to the list" button. Now have a look at the dates in the two drop down boxes and you will see they are different. In code (VBA) Excel stores dates in US format by default, so when it grabs the value for A1 it sees its formatted as a date so thinks it needs to store this as MM/DD/YYYY. In code its not clever enough to look at regional settings while in a normal sheet it will format as a NZ date quite nicely. Hope this helps in your understanding. To confuse matters it all depends on the controls you use as well but I wont delve into that. :-) |
parry (27) | ||
| 1 2 | |||||