Quarterly Log ? How to

[RK]

I am trying to create a workbook that I create two cells
and a command button on sheet1. Cell a1 I would put in a
year (i.e. 2004), Cell b1 I would select a quarter (i.e.
1st, 2nd, 3rd, or 4th) the hit the command button and it
would make changes to sheet2.

Sheet two is a simple quarterly sign off sheet. The
problem I am having is getting the dates and days correct
for each quarter.

i.e. January 2004 cell A7 = Th (for Thursday) Cell b7 = 1

Below is the basic layout for the 1st Quarter of 2004

F1 = 1st Quarter
01 = 2004 (i.e. year)

C4 = January

A7 = Th B7 = 1
A8 = Fr B8 = 2
A9 = Sa B9 = 3
A10 = Su B10 = 4
A11 = Mo B11 = 5
A12 = Tu B12 = 6
A13 = We B13 = 7
A14 = Th B14 = 8

Continues like above Thru A30 = Sa B30 = 24

F3 = Su G3 = 25 continues thru F9 = Sa G9 = 31


H14 = February

F17 = Su G17 = 1 continues thru F30 = Sa G30 = 14


K3 = Su L3 = 15 continues thru K17 = Su L17 = 29


M22 = March

k25 = Mo L25 = 1 continues thru K30 = Sa L30 = 6

P3 = Su Q3 = 7 continues thru p27 = We Q27 = 31

Currently I am having to due this manually, what a pain.

Any help would be appreciated.

Thanks RK

 

[Patrick Molloy]

just put the date in A7 and in b7 put =A7
A8 = A7+1
repeat A8 down for 30 more rows & copy down B7 too
FORMAT column A to "DDD" and format B to "dd"

The format will reveal the day DDD is the three letter
abbreviation. dd gives the day number

You really don't need code for this as its relatively
simple spreadsheet work.

Patrick Molloy
Microsoft Excel MVP