link to calendar
 

i would like to map a spreadsheet to a calendar so that as we track required
staff levels to acutal the daa in the cells would populate a calendar.

Any thoughts? Let me know if I am not clear.

thank you, Ann

link to calendar
 

i would like to map a spreadsheet to a calendar so that as we track
required staff levels to acutal the daa in the cells would populate a
calendar.

In the approach described below, Sheet2 has the staff levels and Sheet1
has the calendar.

In Sheet2, use these columns:
A: Name of a project
B: First date of the project
C: Last date of the project
D: Required staff level of the project
Put the source data in these columns.

Now go to Sheet1.

In A1 put the date where the calendar should start; e.g.,
01/01/2009

B1:M1 will be the column headings; one column per month of the calendar.

In B1, put
=EOMONTH($A$1,COLUMN()-2)
and copy rightward to M1. Select B1:M1 and use
Format Cells Custom
With this as the "Type"
mmm yy

A1:A32 will indicate days of the month; one row per day.

In A2 put
=ROW()-1
and copy downward to A32.

To fill in the calendar, start by putting this in B2:
=IF(DATE(YEAR(B$1),MONTH(B$1),$A2)B$1,"",
SUMIF(Sheet2!$C:$C,"="&DATE(YEAR(B$1),MONTH(B$1), $A2),
Sheet2!$D:$D)-
SUMIF(Sheet2!$B:$B,""&DATE(YEAR(B$1),MONTH(B$1),$ A2),
Sheet2!$D:$D))
Then extend B2 right to M2. Then extend B2:M2 down to B32:M32.

If you need to blank out weekend days, insert a test like
IF(WEEKDAY(...),...).

Modify to suit.

(I have Excel 2003.)

link to calendar
 

I will give it a try and let you know.

Thanks!!!

"MyVeryOwnSelf" wrote:

i would like to map a spreadsheet to a calendar so that as we track
required staff levels to acutal the daa in the cells would populate a
calendar.

In the approach described below, Sheet2 has the staff levels and Sheet1
has the calendar.

In Sheet2, use these columns:
A: Name of a project
B: First date of the project
C: Last date of the project
D: Required staff level of the project
Put the source data in these columns.

Now go to Sheet1.

In A1 put the date where the calendar should start; e.g.,
01/01/2009

B1:M1 will be the column headings; one column per month of the calendar.

In B1, put
=EOMONTH($A$1,COLUMN()-2)
and copy rightward to M1. Select B1:M1 and use
Format Cells Custom
With this as the "Type"
mmm yy

A1:A32 will indicate days of the month; one row per day.

In A2 put
=ROW()-1
and copy downward to A32.

To fill in the calendar, start by putting this in B2:
=IF(DATE(YEAR(B$1),MONTH(B$1),$A2)B$1,"",
SUMIF(Sheet2!$C:$C,"="&DATE(YEAR(B$1),MONTH(B$1), $A2),
Sheet2!$D:$D)-
SUMIF(Sheet2!$B:$B,""&DATE(YEAR(B$1),MONTH(B$1),$ A2),
Sheet2!$D:$D))
Then extend B2 right to M2. Then extend B2:M2 down to B32:M32.

If you need to blank out weekend days, insert a test like
IF(WEEKDAY(...),...).

Modify to suit.

(I have Excel 2003.)