CALENDAR CREATION

[gATOR gIRL]

I HAVE A CALENDAR SET UP BY WEEK, WITH COLUMNS FOR EACH DAY, AND THE FIRST OF
THE YEAR DATE, SUCH AS 1/1/10, IN CELL C2.
_________________________________________________________________
C2 1/1/10
________________________________________________________________
DATE? | SUNDAY DATE? | MONDAY DATE? |WEDNESDAY etc

I WANT TO CREATE A FORMULA THAT WILL LOOK AT THE DATE IN C2, AND THEN ASSIGN
THE PROPER DATES FOR EACH COLUMN, BASED ON THE DAY OF THE WEEK. IF THE
FIRST FALLS ON A WEDNESDAY, I'D WANT 0 AS THE DATE FOR SUNDAY, MONDAY TUESDAY.

I'M WORKING IN EXCEL 2003 AT HOME, BUT WILL THEN SEND TO WORK, WHERE IT WILL
BE IN EXCEL 2007.
THANKS

 

[Pete_UK]

You only really need to put the year in C2, and then you could derive
all the other dates from that. I'm assuming that you would want Sunday
in column A, and that this is to appear on row 4. Put this in A4:

=IF(WEEKDAY(DATE($C2,1,1))>1,"",DATE($C2,1,1))

Then put this in B4:

=IF(A4<>"",A4+1,IF(WEEKDAY(DATE($C2,1,1))>COLUMN(B1),"",DATE($C2,1,1)))

This can then be copied across to F4, and then put this in G4:

=IF(F4<>"",F4+1,DATE($C2,1,1))

All subsequent dates just need 1 to be added to the previous date.

Hope this helps.

Pete