is there a standard formula for this?

[keith]

Please can anyone point me to the best way to do this, I expect it's a
common problem and my head is spinning trying to work out the simple formula
needed:

I have a sheet with rows showing tasks in a project, each task has
columns showing a start & a finish date. Other columns show months.
I need to show how many days of each task occur in each month, so I can
bill for time spent each month.

eg, as in a gantt bar chart:
task start finish Jan Feb March
design 1 jan 31 Jan 31 0 0
develop 1 jan 20 Feb 31 20 0
etc.

It must be something like "startofmonth-taskstart..........etc" but there
seem to be 6 different combinations of how a task overlaps the month you
want, and I'm looking for a simple calculation, not 6 nested Ifs!

Thanks if anyone can help.

[Biff]

Hi!

Try this:

Column B = start dates (must be true Excel dates)
Column C = finish dates (must be true Excel dates)

E1:P1 = Jan, Feb, Mar etc

Formula in E2:

=IF(MAX($B2,DATE(YEAR($B2),MONTH($B2)+COLUMNS($B:B)-1,1))<$C2,MIN(DATE(YEAR($B2),MONTH($B2)+COLUMNS($B:B),0),$C2)-MAX($B2,DATE(YEAR($B2),MONTH($B2)+COLUMNS($B:B)-1,1))+1,0)

Copy across then down as needed.

Note: only works properly if the dates are in the same year!

Biff

"keith" <keith@nospam.com> wrote in message
news:dltab6$je6$1@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
> Please can anyone point me to the best way to do this, I expect it's a
> common problem and my head is spinning trying to work out the simple
> formula needed:
>
> I have a sheet with rows showing tasks in a project, each task has
> columns showing a start & a finish date. Other columns show months.
> I need to show how many days of each task occur in each month, so I can
> bill for time spent each month.
>
> eg, as in a gantt bar chart:
> task start finish Jan Feb March
> design 1 jan 31 Jan 31 0 0
> develop 1 jan 20 Feb 31 20 0
> etc.
>
> It must be something like "startofmonth-taskstart..........etc" but there
> seem to be 6 different combinations of how a task overlaps the month you
> want, and I'm looking for a simple calculation, not 6 nested Ifs!
>
> Thanks if anyone can help.
>
>
>
>
>
>