is there a standard formula for this?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

keith - 21 Nov 2005 20:23 GMT

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.

Reply to this Message

Biff - 22 Nov 2005 03:48 GMT

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

> 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
[quoted text clipped - 16 lines]
>
> Thanks if anyone can help.

Reply to this Message