By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,164 Members | 1,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,164 IT Pros & Developers. It's quick & easy.

Convert date range into separate months - ideas?

P: n/a
I have a table with projects that have a start date, an end date and
total hours, for example:

Project StartDate EndDate Hours
Plumbing 3/16/07 5/3/07 240
Drywall 2/3/07 3/4/07 94
Electrical 3/20/07 4/4/07 86

What I want is to have a report that shows me how many hours each
month are spent on a particular project, assuming that regardless of
the particular day of the month the project was started or ended, the
project's hours are evenly divided among months (for example, above
Plumbing should be divided evenly among March, April, and May, even
though the project only extends 3 days into May). The Report I
eventually end up with I want to look like this:

Project 2/07 3/07 4/07 5/07
Plumbing 0 80 80 80
Drywall 47 47 0 0
Electrical 0 43 43 43

How do I take a table with start date and end date columns and convert
it into months? I'm dealing with several thousand records, so I really
would like to avoid doing this manually! Thanks for any ideas!

Sep 11 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
i haven't tried it, but you might be able to get the result you want with a
crosstab query, using a formatted StartDate, as

MyDate: Format([StartDate],"m\/yy")

hth
<we***@brawnandfervor.comwrote in message
news:11*********************@y42g2000hsy.googlegro ups.com...
I have a table with projects that have a start date, an end date and
total hours, for example:

Project StartDate EndDate Hours
Plumbing 3/16/07 5/3/07 240
Drywall 2/3/07 3/4/07 94
Electrical 3/20/07 4/4/07 86

What I want is to have a report that shows me how many hours each
month are spent on a particular project, assuming that regardless of
the particular day of the month the project was started or ended, the
project's hours are evenly divided among months (for example, above
Plumbing should be divided evenly among March, April, and May, even
though the project only extends 3 days into May). The Report I
eventually end up with I want to look like this:

Project 2/07 3/07 4/07 5/07
Plumbing 0 80 80 80
Drywall 47 47 0 0
Electrical 0 43 43 43

How do I take a table with start date and end date columns and convert
it into months? I'm dealing with several thousand records, so I really
would like to avoid doing this manually! Thanks for any ideas!

Sep 11 '07 #2

P: n/a
Wow. Thank you, this works beautifully!!

Sep 12 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.