TODAY() issues

[Keyrookie]

Help please! I have a calendar that I'm trying to get the current date
to change format by using a conditional formula and TODAY(). I think
there is something in the programing that won't allow this operation.
The spreadsheet is set up like this:

cell B2 = 1/1/2007
cell J2 =DATE(YEAR(B2),MONTH(B2)+1,1) (This gives me my month, Feb.)

cell J4 =(WEEKDAY(J2)=1)*J2 (This gives me my 1st day of the month)
cell K4 =(WEEKDAY(J2)=2)*J2+(J4>0)+J4 (This gives the 2nd day)
cell L4 =(WEEKDAY(J2)=3)*J2+(K4>0)+K4 (This gives the 2rd day)
cell M4 =(WEEKDAY(J2)=4)*J2+(L4>0)+L4 (This gives the 4th day)
cell N4 =(WEEKDAY(J2)=5)*J2+(M4>0)+M4 (The 5th day)
cell O4 =(WEEKDAY(J2)=6)*J2+(N4>0)+N4 (The 6th day)
cell P4 =(WEEKDAY(J2)=7)*J2+(O4>0)+O4 (The 7th day)
cell J5 =+P4+1 (The 8th day)
cell K5 =+J5+1 (The 9th day)
cell L5 =+K5+1 (The 10th day)

This +1 of the previous cell continues to the end of the month. All 12
months are set up the same way.

What am I doing wrong? Can't get the conditional formula to change the
current day in the month to a different format ..... very frustrating!

Thanks for your help.

 

[Bob Phillips]

J4: =IF(WEEKDAY($J$2)=1,$J$2,"")
K4: =IF(J4<>"",J4+1,IF(WEEKDAY($J$2)=COLUMN(B1),$J$2,""))
copy K4 across to P4
J5: =IF(P4="","",P4+1)
K5: =IF(J5="","",IF(MONTH(J5+1)<>MONTH($J$2),"",J5+1))
copy K5 across to P5
copy J55 down to J5:J10

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Keyrookie]

Thanks Bob,

I tried your solution and I get "#VALUE" in each cell. It also change
condition 1 of conditional formatting. This file is a perpetua
calendar so I just have to adjust cell B2 to Jan. 1 of any year and th
calendar will populate with the correct dates. I don't know if tha
will make any difference .... I would be happy to email the fil
directly to you if that would help.

I'm still wanting to conquer this problem so any help will b
appreciated.

Thanks,

Keyrookie

 

[Bob Phillips]

That suggests to me that J2 is not a valid date. Check it out.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Keyrookie]

Bob,

J2 is directly referencing B2 which is a date that is typed in, ie.
"1/1/2007". Everything else in the spreadsheet works fine, even the
other conditional formula. I just can't get the TODAY command to work.
Is is possible to email the file to you directly? I'm sure you could
solve it quickly.

Still frustrated,

Keyrookie

 

[Peo Sjoblom]

Tools>options>transition and uncheck any check marks

 

[Keyrookie]

Peo,

Thanks for your response. I checked the transition option and ther
are none of the options checked. Please help!!

Keyrookie