Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Computing first and end of month (2003)

    I have a date field in my data that is a service date. I need to to do 2 computations : I need to take the service date and convert it to the end of the month and another computation where I find the beginning of the month. How would I accomplish this? Thanks.

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Computing first and end of month (2003)

    Try this:

    To get the end of the Service Date : =DATE(YEAR([Service Date ]),MONTH([Service Date ])+1,1)-1
    To get the beginning of the Service Date : =DATE(YEAR([Service Date]),MONTH([Service Date]),1)
    Regards,
    Rudi

  3. #3
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Computing first and end of month (2003)

    thank you.

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

    Re: Computing first and end of month (2003)

    Rudi, those are Excel formulas, not Access formulas. The Access (=VBA) function to compute a date is DateSerial, not Date. So:

    =DateSerial(Year([Service Date ]),Month([Service Date ])+1,1)-1

    and

    =DateSerial(Year([Service Date]),Month([Service Date]),1)

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

    Re: Computing first and end of month (2003)

    A slight variation to get the end of service date:

    =DateSerial(Year([Service Date ]),Month([Service Date ])+1,0)

  6. #6
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Computing first and end of month (2003)

    Thanks Hans

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Computing first and end of month (2003)

    Thanks for pointing that out. I just typed the formula into the post without thinking that Access uses a different variation from Excel.
    Sorry Benji if that was initially a bit misleading.
    Regards,
    Rudi

Posting Permissions

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