is there a standard formula for this?
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. |
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. |
All times are GMT +1. The time now is 11:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com