HELP: if cell is first monday of the month then display month

[bourne1978]

Hi, I have a columns displayed that are the first Monday of every week for 52 weeks; above that column I want to display the month if it is the first monday of the month. I can't figure it out. Please help.

 

[Rick Rothstein]

Hi, I have a columns displayed that are the first Monday of every week for 52 weeks; above that column I want to display the month if it is the first monday of the month. I can't figure it out. Please help.

Are the displayed "first Monday of every week" real Excel dates (that is, not text), then just use the MONTH function...

=MONTH(A2)

If they are not real Excel dates, then show us two or three samples of the values you have in the cells.

 

[bourne1978]

It kinda looks like this (see table), I don't want Sep repeating for the next 4 columns, I just want it to be able in the 52 week column, to only show a month if the cell is the First Monday of that month.

Sep					OCt				Nov
2	9	16	23	30	7	14	21	28	4

<tbody>
</tbody>

 

[Rick Rothstein]

It kinda looks like this (see table), I don't want Sep repeating for the next 4 columns, I just want it to be able in the 52 week column, to only show a month if the cell is the First Monday of that month.

Sep					OCt				Nov
2	9	16	23	30	7	14	21	28	4

<TBODY>
</TBODY>

Are the 2, 9, 16, etc. just numbers or are they real Excel dates formatted to display the day number only? Also, are the 2, 9, 16, etc. typed in manually or are they the result of a formula? If a formula, please show it to us.

 

[bourne1978]

thanks! they are date format, but I set in "the number format menu" to display only "dd".

 

[jeffreybrown]

Here is a possibliity...

Make A1 equal to A2 but format as mmm

Conditional Formatting

• Highlight applicable range >> B1:R1
• Home Tab >> Styles >> Conditional Formatting
• New Rule >> Use a formula to determine which cells to format
• Edit the Rule Description: Format values where this formula is true: =MONTH(A1)=MONTH(B1)
  
• Format… [Number, Font, Border, Fill] >> White Font
• OK >> OK

Data Range

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

R​

1​

Sep​

​

​

​

​

Oct​

​

​

​

Nov​

​

​

​

Dec​

​

​

​

​

2​

02​

09​

16​

23​

30​

07​

14​

21​

28​

04​

11​

18​

25​

02​

09​

16​

23​

30​

 

[Rick Rothstein]

Since the values in Row 2 are real dates, then you could do this...

A1: =TEXT(A2,"mmm")

B1: =IF(MONTH(B2)=MONTH(A2),"",TEXT(B2,"mmm"))

Copy B1 across to the end of your dates.

 

[bourne1978]

sorry, I took so long. but it worked! thanks! I even did it for the Year too, as I increase my columns to be a 2 year time frame, 104 weeks.

 

[bourne1978]

the second one, worked really well.