Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Accounting Calendar (2000)

    Our firm has an accounting calendar , period 1 is the first 4 weeks of the year, period 2 is the next 4 weeks after that, and period 3 is the next 5 weeks after that. This is repeated 4 times, so period date ranges would be :-

    P1 - 29/12/02 - 24/01/02 (4 weeks)
    P2 - 25/01/02 - 21/02/02 (4 weeks)
    P3 - 22/02/02 - 28/03/02 (5 weeks)

    I'm trying to set up 12 buttons on a form that will automatically put these date ranges into a Begin_Date field, and End_Date field. (for the current year)

    Now.. One method would be to just create a table, and put all these dates in.. but, i want it to work every year.

    Strangely, i know how i could do this in Excel, because Excel turns each day into a distinct number, that can be used, but as far as i'm aware, Access doesn't have this functionality.

    I can grab the current year using <font color=blue>DatePart("yyyy,now())</font color=blue> i think. but this doesnt really help that much. I'm thinking that perhaps if i can calculate the first day of the year, then after that i can just add days to get the required dates.

    However, the first day of the year is going to vary from year to year.... <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>

    Ok,, how about this idea.. I tell it the first day of 2000, then add 365 days to get 2001, <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> leap years would muck up that idea a bit.

    Or maybe there is a better way ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Accounting Calendar (2000)

    Steve,

    Internally, dates are just numbers in both Excel and Access.

    What is your definition of the "first day of the year" for accounting? Here in the Netherlands, we use ISO 8601: the "first day of the year" for administrative purposes is the Monday in the first week containing 4 or more days in that year, or, equivalently, the week containing the 4th of January. In 2003, the 4th of January was a Saturday, so the "first day of the year" for 2003 was Monday, 30 December 2002.

    If you specify your definition, I can give you a formula.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Accounting Calendar (2000)

    One additional wrinkle is that about every 5 years you need to have a 6-week period to keep the accounting calendar more or less in sync with the Julian calendar. That stems from the fact that 52 weeks equals 364 days, not 365 (or 366).
    Wendell

  4. #4
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accounting Calendar (2000)

    Hmm, im not exactly sure how its worked out. i will try to find out more information..

    if i can get it to work for the next 2-3 years then that would be a success.

    2003 - first day is Sat 29 th Dec 2002, this is start of period 1, and ends 28 days later on 24/01/03
    2004 - first day is Sat 27th Dec 2003,

    So its roughly equivalent, to the first saturday in the week containing the 1st of january.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Accounting Calendar (2000)

    I may have an idea, but before I post it, can you clear up "Sat 29 th Dec 2002"? It's either "Sat 28 th December 2002" or "Sun 29 th December 2002".
    And 28 days later is not 24/01/03 in either of these cases.
    I'm not doing this to annoy you, but because it's important to know the exact requirements.

  6. #6
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accounting Calendar (2000)

    Sorry Hans, me bad...

    Saturday 28 dec 2002, to fri 24/01/03 is <font color=blue>Period One 2003 </font color=blue> (This should be exactly 28 days)

    Saturday 27 dec 2003, is the first day of the year for 2004 start

    The first day of the year is always a Saturday, the number of days in the periods always follows this sequence.

    28 - 28 - 35 - 28 - 28 - 35 - 28 - 28 - 35 -28 - 28 - 35

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Accounting Calendar (2000)

    <P ID="edit" class=small>(Edited by HansV on 28-Mar-03 15:49. Corrected mistake in last day of period)</P>OK, here you go (first attempt).

    Put these functions in a standard module.

    Function FirstDayOfYear(intYear As Integer) As Date
    Dim datJan1 As Date
    Dim intDayOfWeek As Integer
    datJan1 = DateSerial(intYear, 1, 1)
    intDayOfWeek = WeekDay(datJan1, vbSaturday)
    FirstDayOfYear = datJan1 + 1 - intDayOfWeek
    End Function

    Function StartOfPeriod(intYear As Integer, intPeriod As Integer) As Date
    Dim datFirstDay As Date
    Dim intOffset As Integer
    datFirstDay = FirstDayOfYear(intYear)
    intOffset = 4 * (intPeriod - 1) + (intPeriod - 1) 3
    StartOfPeriod = datFirstDay + 7 * intOffset
    End Function

    The first function calculates the "first day of the year" as the Saturday in the week containing the 1st of January.
    The second function calculates the start of the n-th period in a year, where n = 1, 2, ..., 12.

    Example: to get the start of the 5th period for 2003, use
    Period5:StartOfPeriod(2003,5)
    in a query, or
    =StartOfPeriod(2003,5)
    as control source of a text box on a form or report.

    To find the last day of a period, just subtract 1 from the first day of the next period, or in the case of the twelfth (and last) period of the year, subtract 1 from the first day of the first day of the next period. (The last period may be 6 weeks)

    IMPORTANT: Test very carefully if this returns the correct dates. I looks OK to me, but I don't know the exact situation.

  8. #8
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accounting Calendar (2000)

    <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> How will you handle the missing day a year? Throw in the leap year and your financial year must drift by 5 days every 4 years! <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Accounting Calendar (2000)

    The 12th and last period in a year will be 6 weeks long from time to time, to compensate for this. If you calculate the start of period n for n = 1 ... 12 using the function I posted above (in <post#=239263>post 239263</post#>), this will be handled automatically.

  10. #10
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accounting Calendar (2000)

    The function works great Hans, however, i'm having trouble passing the correct year to the function..

    I've tried....

    <font color=blue>Public Function PeriodSelect(period As Integer)
    Dim yr As Date, yr1 As Date
    yr1 = Now()
    yr = year(yr1)
    Forms!Switchboard!Begin_Date = StartOfPeriod(yr, period)
    Forms!Switchboard!Finish_Date = StartOfPeriod(yr, period + 1) - 1
    End Function</font color=blue>


    also tried <font color=blue>yr = DatePart("yyyy",now())</font color=blue>

    Its coming up with a type mismatch.. but if i do this all in the immediate window, i get the correct result "2003"

    I can only assume its something to do with it not being a date format when it expects one ?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Accounting Calendar (2000)

    You should declare

    Dim yr As Integer

    instead of

    Dim yr As Date

  12. #12
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accounting Calendar (2000)

    doh!

    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    Cheers

    Lined up another for you... <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15> <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15> <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15> <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15> <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15> <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15> <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15> <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •