# Thread: Calculating a number by looking at two date fields

1. Registered User
Join Date
Oct 2003
Posts
5

## 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. Registered User
Join Date
Apr 2003
Posts
280
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. Registered User
Join Date
Oct 2003
Posts
5

## 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. Registered User
Join Date
Aug 2003
Location
SW Ohio
Posts
198

## Re: Thanks, but I run into the following problem

datediff("d",<startdate>,<enddate>)

#### Posting Permissions

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