var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
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.
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.
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.
Re: Thanks, but I run into the following problem
Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns