If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
QDE (Quick Date Entry)
Hi Community of Excel Users!
This is a joint posting from 4 of the "regulars" to these newsgroups: Bob Phillips, Frank Kabel, Ron de Bruin, and Norman Harker. QDE.xla Version 1 is now available *free* from: Bob Phillips http://www.xldynamic.com/source/xld.QDEDownload.html and Ron de Bruin http://www.rondebruin.nl/qde.htm A long outstanding feature request in these newsgroups has been for a robust method by which dates can be entered as numbers only. By "robust" we mean a method that will stand up to the fullest possible range of Operating System settings. Although this is Version 1, it has been tested as much as possible within the constraints we faced. However, we believe that it is now ready for release and we hope that the community of Excel users will appreciate what we have tried to achieve and will have no problems with using it. It comes with the usual full money back if not delighted guarantee; "You pay nothing for it and you'll get nothing back!" Edited thrilling highlights of what QDE is all about are provided below our sign-off with further details in the Help File and at the link above. Regards Norman Harker, Sydney, Australia Bob Phillips, Poole, England Frank Kabel, Frankfurt, Germany Ron de Bruin, Netherlands ***** Edited Highlights of QDE *** Background: This is a joint effort of 4 of the "regulars" Bob Phillips, Frank Kabel, Ron de Bruin and Norman Harker. *** Why QDE? Entry of dates into Excel is not too difficult to master for any given set of Operating System Regional Settings although users have to appreciate the inbuilt assumptions that Excel makes regarding various date entry forms. However, where large numbers of dates need to be entered, Excel's date serial number approach imposes obligations to use date separators (eg. 03-05-2004). Where many dates are required to be entered, this imposes a requirement for more key presses than might be achieved. For example, we might prefer just to enter 0305 and have this interpreted "correctly" as 03-05-04. If we can do this, we save 4 key presses. Big deal? Not really! Unless, that is, you have many hundreds or even thousands of such dates to enter. This is why we need QDE. We need a means by which we can economize on the number of keystrokes required to enter dates. *** What Is QDE? QDE is an Excel Addin that provides quick input of dates, in all international formats. With QDE you now have the option to enter dates using numbers only. These are interpreted by QDE as dates based upon settings which you will rarely, if ever, need to change. QDE is a stand-alone utility, that once installed is available to all of your workbooks and worksheets. It does not change anything on the worksheet until directed to by you the user. Once dates are entered they are treated by Excel as dates entered using non-QDE methods. Workbooks constructed with the assistance of QDE do not require that QDE should remain installed or even be present on whatever computer the workbook is open on. *** Where Did It Come From? Date entry has been a perennial problem in Excel. Many people want to input the date components (day. month, year) without having to bother with the date delimiters. The problem here is that Excel then interprets this as a normal number, not a date. Chip Pearson made the first serious attempt at a solution that we know of, which you can see at Chip's site on his Date and Time Entry page, Whilst it does show some of the issues that need to be addressed, it is US date centric, and there are a couple of problems with the input. Norman Harker decided that a full international version of this routine was needed. Norman is an Excel MVP based in Sydney, Australia and is a regular contributor to the Excel newsgroups. He has a special interest in Excel's date handling capabilities. Norman posted a tentative solution which was hitting problems. There were many follow up responses to this query, mainly from Frank Kabel, based in Frankfurt, Germany and Bob Phillips, based in Poole, England. Both are regular contributors to the Excel newsgroups. The discussions moved the problem on somewhat, but as usual, further issues were revealed as the "regulars" educated themselves on the complexities involved. At this point, Norman, Frank and Bob decided informally that the newsgroups were not the best forum for development work and informally agreed to set up a team to tackle the problem. Ron de Bruin, another Excel MVP, was recruited to provide the extra skills and experience needed, and QDE was conceived. There were many problems along the way to what we have here. These were mainly those of ensuring that all international issues, operating system settings and Excel versions were covered. This is the result of that development, and we hope that you enjoy it and find it useful. If you are interested, you can see the original newsgroup discussion that gave rise to QDE at this Google thread http://tinyurl.com/3luwe *** How It Works This is a classic case of, "Easier said than done!" Excel has limited date interpretation capabilities that are inherent in Excel not using a dedicated data type (as is common for database programs). Excel uses the date serial number concept whereby dates are nothing more than a format of a number with 1 representing a base date. However, users, want to enter dates quickly with as few numbers and key presses as is possible, and to have Excel interpret those numbers as the dates that the user intends. The trouble is that there are three interacting issues: 1. There are two date serial number bases in Excel; the 1900 Date System where 1 represents 1-Jan-1900 and the 1904 Date System where 1 represents 1-Jan-1904. The 1900 Date System is the default setting for Windows and the 1904 Date System is the default setting for Mac. But Excel for Windows also allows you to change the default setting to the 1904 Date System. 2. Ordering the three parameters of day month and year varies according to the user's country custom. In the US (predominantly) entry is Month Day Year. In some Asian countries and in countries that have adopted the International Standard ISO8601:2000 the order is Year Month Day. The rest of the world (when using the Gregorian Calendar), including most of Europe, UK, South America and former British Colonies all use Day Month Year. 3. The quick date entry of a user might be from 3 to 8 digits long depending upon whether or not it was a single or double digit day or month number and whether or not they were entering a double digit or four digit year (or, indeed, wanted the current year to be assumed). QDE handles quick date entry interpretation and reflects these three interacting issues. In most situations those issues are handled only once for a given range or worksheet and most often the user will not even have to change the parameters that QDE takes from current Excel and Operating System settings. End of Post. |
#2
|
|||
|
|||
very many thanks. I shall download the addin. meanwhile my gratitude to
you guys for making our lives easier. thanks again;. Norman Harker wrote in message ... Hi Community of Excel Users! This is a joint posting from 4 of the "regulars" to these newsgroups: Bob Phillips, Frank Kabel, Ron de Bruin, and Norman Harker. QDE.xla Version 1 is now available *free* from: Bob Phillips http://www.xldynamic.com/source/xld.QDEDownload.html and Ron de Bruin http://www.rondebruin.nl/qde.htm A long outstanding feature request in these newsgroups has been for a robust method by which dates can be entered as numbers only. By "robust" we mean a method that will stand up to the fullest possible range of Operating System settings. Although this is Version 1, it has been tested as much as possible within the constraints we faced. However, we believe that it is now ready for release and we hope that the community of Excel users will appreciate what we have tried to achieve and will have no problems with using it. It comes with the usual full money back if not delighted guarantee; "You pay nothing for it and you'll get nothing back!" Edited thrilling highlights of what QDE is all about are provided below our sign-off with further details in the Help File and at the link above. Regards Norman Harker, Sydney, Australia Bob Phillips, Poole, England Frank Kabel, Frankfurt, Germany Ron de Bruin, Netherlands ***** Edited Highlights of QDE *** Background: This is a joint effort of 4 of the "regulars" Bob Phillips, Frank Kabel, Ron de Bruin and Norman Harker. *** Why QDE? Entry of dates into Excel is not too difficult to master for any given set of Operating System Regional Settings although users have to appreciate the inbuilt assumptions that Excel makes regarding various date entry forms. However, where large numbers of dates need to be entered, Excel's date serial number approach imposes obligations to use date separators (eg. 03-05-2004). Where many dates are required to be entered, this imposes a requirement for more key presses than might be achieved. For example, we might prefer just to enter 0305 and have this interpreted "correctly" as 03-05-04. If we can do this, we save 4 key presses. Big deal? Not really! Unless, that is, you have many hundreds or even thousands of such dates to enter. This is why we need QDE. We need a means by which we can economize on the number of keystrokes required to enter dates. *** What Is QDE? QDE is an Excel Addin that provides quick input of dates, in all international formats. With QDE you now have the option to enter dates using numbers only. These are interpreted by QDE as dates based upon settings which you will rarely, if ever, need to change. QDE is a stand-alone utility, that once installed is available to all of your workbooks and worksheets. It does not change anything on the worksheet until directed to by you the user. Once dates are entered they are treated by Excel as dates entered using non-QDE methods. Workbooks constructed with the assistance of QDE do not require that QDE should remain installed or even be present on whatever computer the workbook is open on. *** Where Did It Come From? Date entry has been a perennial problem in Excel. Many people want to input the date components (day. month, year) without having to bother with the date delimiters. The problem here is that Excel then interprets this as a normal number, not a date. Chip Pearson made the first serious attempt at a solution that we know of, which you can see at Chip's site on his Date and Time Entry page, Whilst it does show some of the issues that need to be addressed, it is US date centric, and there are a couple of problems with the input. Norman Harker decided that a full international version of this routine was needed. Norman is an Excel MVP based in Sydney, Australia and is a regular contributor to the Excel newsgroups. He has a special interest in Excel's date handling capabilities. Norman posted a tentative solution which was hitting problems. There were many follow up responses to this query, mainly from Frank Kabel, based in Frankfurt, Germany and Bob Phillips, based in Poole, England. Both are regular contributors to the Excel newsgroups. The discussions moved the problem on somewhat, but as usual, further issues were revealed as the "regulars" educated themselves on the complexities involved. At this point, Norman, Frank and Bob decided informally that the newsgroups were not the best forum for development work and informally agreed to set up a team to tackle the problem. Ron de Bruin, another Excel MVP, was recruited to provide the extra skills and experience needed, and QDE was conceived. There were many problems along the way to what we have here. These were mainly those of ensuring that all international issues, operating system settings and Excel versions were covered. This is the result of that development, and we hope that you enjoy it and find it useful. If you are interested, you can see the original newsgroup discussion that gave rise to QDE at this Google thread http://tinyurl.com/3luwe *** How It Works This is a classic case of, "Easier said than done!" Excel has limited date interpretation capabilities that are inherent in Excel not using a dedicated data type (as is common for database programs). Excel uses the date serial number concept whereby dates are nothing more than a format of a number with 1 representing a base date. However, users, want to enter dates quickly with as few numbers and key presses as is possible, and to have Excel interpret those numbers as the dates that the user intends. The trouble is that there are three interacting issues: 1. There are two date serial number bases in Excel; the 1900 Date System where 1 represents 1-Jan-1900 and the 1904 Date System where 1 represents 1-Jan-1904. The 1900 Date System is the default setting for Windows and the 1904 Date System is the default setting for Mac. But Excel for Windows also allows you to change the default setting to the 1904 Date System. 2. Ordering the three parameters of day month and year varies according to the user's country custom. In the US (predominantly) entry is Month Day Year. In some Asian countries and in countries that have adopted the International Standard ISO8601:2000 the order is Year Month Day. The rest of the world (when using the Gregorian Calendar), including most of Europe, UK, South America and former British Colonies all use Day Month Year. 3. The quick date entry of a user might be from 3 to 8 digits long depending upon whether or not it was a single or double digit day or month number and whether or not they were entering a double digit or four digit year (or, indeed, wanted the current year to be assumed). QDE handles quick date entry interpretation and reflects these three interacting issues. In most situations those issues are handled only once for a given range or worksheet and most often the user will not even have to change the parameters that QDE takes from current Excel and Operating System settings. End of Post. |
#3
|
|||
|
|||
Now XL2K SP-3.
I'll admit I'm torture testing now. I select B2:C7 as QDE entry range in an activated worksheet. I select that entire range, type 1 and press [Ctrl]+[Enter]. All cells contain 1. I have to select individual cells to get them formatted as dates, but this doesn't trigger invalid date entry. Also, every cell in this range displays 12/31/1899 when it's the ActiveCell, so there's definitely a bug in the SelectionChange event handler - even in XL2K. However, single cell entry in multiple cell selections does work. |
#4
|
|||
|
|||
Harlan Grove wrote: Now XL2K SP-3. I'll admit I'm torture testing now. vbg yes you're are but that's totally o.k. I select B2:C7 as QDE entry range in an activated worksheet. I select that entire range, type 1 and press [Ctrl]+[Enter]. All cells contain 1. I have to select individual cells to get them formatted as dates, but this doesn't trigger invalid date entry. Also, every cell in this range displays 12/31/1899 when it's the ActiveCell, so there's definitely a bug in the SelectionChange event handler - even in XL2K. I tracked this annoying display error down and this is again Excel's one-day of error for dates prior to 01-March-1900. The VBA function Format does interprete '1' as 31-Dec-1899. So VBA is again handling some values differently than the worksheet function. (e.g. try ?Format(1,"MM/DD/YYYY") in the intermediate window. Put this on our bug list to create a workaround for this behaviour. We will also consider multi-cell entries (with CTRL+ENTER) as a possible enhancement for the next version However, single cell entry in multiple cell selections does work. Relieved :-)) Frank |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Aggregating Date Data into Weeks and Quarters | Roger | Running & Setting Up Queries | 3 | July 11th, 2004 05:56 PM |
If statement | Doug | Worksheet Functions | 9 | June 28th, 2004 06:13 AM |
Date & Text Combination Entry | Steve Quiroz | General Discussion | 1 | June 21st, 2004 05:19 PM |
click on check box to add an entry date | Greg | Using Forms | 4 | June 2nd, 2004 06:53 PM |
Does date fall between two ranges? | MR | Worksheet Functions | 4 | January 14th, 2004 04:08 PM |