is there a standard formula for this?
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" wrote in message
...
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.
|