Place a number in a calendar like spreadsheet, that places a number every second thursday

[umirin]

A B C

Date

Thu 05 Apr

Fri 06 Apr

Sat 07 Apr

Sun 08 Apr

Mon 09 Apr

Tue 10 Apr

Wed 11 Apr

Thu 12 Apr

Fri 13 Apr

Sat 14 Apr

Sun 15 Apr

Mon 16 Apr

Tue 17 Apr

Wed 18 Apr

Thu 19 Apr

Fri 20 Apr

Number

100.00

100.00

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Hi All I need some help please. I would like a formula to go in B2 Cell, a formulai can fill accross the B row that would place the number 100 in every second Thursday as exampled above.

 

[Peter_SSs]

Try
=IF(AND(WEEKDAY(B1)=5,MOD(SUMPRODUCT(--(WEEKDAY($B1:B1)=5)),2)=1),100,"")

 

[mopp1]

or
=IF(MOD(INT(B1)-5,14)=0,100,"")

 

[Peter_SSs]

or
=IF(MOD(INT(B1)-5,14)=0,100,"")

Not exactly sure what the OP's requirements are, but that won't necessarily pick up the 1st, 3rd, 5th etc Thursday in the list. eg If the date list started on 6 April

 

[umirin]

Try
=IF(AND(WEEKDAY(B1)=5,MOD(SUMPRODUCT(--(WEEKDAY($B1:B1)=5)),2)=1),100,"")

Thanks Pete. Exactly what I was after.

 

[Peter_SSs]

Thanks Pete. Exactly what I was after.

You are welcome. Glad to help.