Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003

    Unanswered: Calculating a number by looking at two date fields

    I have a table containing the fields listed below and am trying to figure out how to calculate the "number of days a campaign ran during the current month". A sample data set is below:

    Campaign Start Date: 10/02/03
    Campaign End Date: 11/31/03

    In the above example, I can look at the dates and figure out in my head that the campaign ran for 30 days in October (10/2 thru 10/31).

    That is straight forward enough, but I don't know how to make a formula calculate the number of dys a campaign ran in the current month.

    I hope this is clear, but to be honest I'm confusing myself even trying to explain it. If you have any advice, I would really appreciate it.

  2. #2
    Join Date
    Apr 2003
    NumberofDays: ((DatePart('d', [CampaignEndDate]) - DatePart('d', [CampaignStartDAte]))+1)

    d is days
    m is months
    yyyy is years

    Put this in your field row or column.

  3. #3
    Join Date
    Oct 2003

    Thanks, but I run into the following problem

    But campaign start dates and campaign end dates could occur in previous years and future years respectively. Example:

    Campaign Start Date: 02/03/1999
    Campaign End Date: 06/07/2006

    I can look at the above and know that for this campaign ran for all of October, but can't figure out how to have a formula tell me that yes this campaign ran for 31 days.

    The logic is something like if campaign start date is less than 1st day of current month and campaign end date is greater than last day of current month, then use number of days in current month.

    If either the start date or end date falls in the month, you need to calculate how many days it did run during the month.

  4. #4
    Join Date
    Aug 2003
    SW Ohio

    Re: Thanks, but I run into the following problem

    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

Posting Permissions

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