Drew Wutka
DWUTKA at Marlow.com
Mon Oct 8 13:40:43 CDT 2007
Not sure if that will actually do the trick. If you are going on a true 4 4 5 routine, eventually, the Saturday will be in a different month. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman Sent: Monday, October 08, 2007 1:05 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Calculating End of Month on a 4-4-5 Schedule Besides which with a lookup table, they can implement whatever type of closing schedule they want (monthly or 4-4-5). For myself, I've always used a hybrid approach; I use a lookup table, but fill that table automatically for them with what I believe to be the correct dates based on what they tell me the last day of the week is and what schedule their using. Finial check then is up to them. I'd be leery of using a totally calculated approach. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka Sent: Monday, October 08, 2007 12:31 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Calculating End of Month on a 4-4-5 Schedule I concur. If you want the last Saturday of any month, I can do that in one line of code, but this 4-4-5 thing is going to shift one day every year (and 2 every leap year). So eventually, you will end up with the start of a fiscal month at the beginning of another month. (Take 2007, if you say that 1-31-2007 is the end of the month, the beginning would actually be 12-31-2006). I'm good at math, but the 4 4 5 thing eludes me how to come up with an equation to calculate it. Seems like the most efficient route would be to just build a look up table. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman Sent: Monday, October 08, 2007 10:40 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Calculating End of Month on a 4-4-5 Schedule It's a standard in the US financial community. It's based on the fact that the year needs to be divided up into 13 week quarters for accounting, yet the calendar varies. The way I typically see this implemented is a table containing the last (or first) fiscal date for each month, and the last day of the fiscal year. It's then easy to determine which month (and hence quarter) a given date falls into. Jim. The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI BusinessSensitve material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI BusinessSensitve material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.