number based on the value of another column.

[lezawang]

Hi

I have a column like the one below. I want to have another column beside it and each friday in column A will have number in column B as below. Is that possible to do it? Thank you very much.

[TABLE="class: grid, width: 169"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]Tue, 20/08/19[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]Wed, 21/08/19[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]Thu, 22/08/19[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]Fri, 23/08/19[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]Sat, 24/08/19[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]Sun, 25/08/19[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]Mon, 26/08/19[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]Tue, 27/08/19[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]Wed, 28/08/19[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]Thu, 29/08/19[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]Fri, 30/08/19[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]Sat, 31/08/19[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]Sun, 01/09/19[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]Mon, 02/09/19[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]Tue, 03/09/19[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]Wed, 04/09/19[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]Thu, 05/09/19[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]Fri, 06/09/19[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

Hi, there are lots of ways to do this, here's one.

Assumptions -
1) Your dates are real Excel dates, not text strings.
2) 20th August is in cell A1.

In B2 . . .

=IF(WEEKDAY(A2)=6,MAX(B$1:B1)+1,"")

and copy down as far as required.

 

[Fluff]

How about

Book1
	A	B
1	Daes	Friday
2	20/08/2019
3	21/08/2019
4	22/08/2019
5	23/08/2019	1
6	24/08/2019
7	25/08/2019
8	26/08/2019
9	27/08/2019
10	28/08/2019
11	29/08/2019
12	30/08/2019	2
13	31/08/2019
14	01/09/2019
15	02/09/2019
16	03/09/2019
17	04/09/2019
18	05/09/2019
19	06/09/2019	3
Test

Cell Formulas
Range	Formula
B2	=IF(WEEKDAY(A2)<>6,"",COUNTIF(B$1:B1,">=0")+1)

 

[lezawang]

Thank you all for the help.

Can you tell me please what does B$1:B1 mean here?

 

[Fluff]

It's a range of cells. Have a look at the formula in (say) B12.
What is it there?

 

[lezawang]

Both formulas print only 1 infront any cell which has Friday

and B12 is

=IF(WEEKDAY(A11)<>6,"",COUNTIF(B$1:B10,">=0")+1)

 

[Gerald Higgins]

Both formulas print only 1 infront any cell which has Friday

Mine doesn't, on my machine, it shows 1 for the first Friday, 2 for the 2nd Friday, 3 for the 3rd Friday, and so on.
I haven't tested Fluff's solution but it looks similar to mine and I think it will work correctly as well, if you set it up correctly.

For my solution -
Your dates need to be in column A, starting in A1.
The formula I provided needs to be in B2, and then copied down.
If any of these addresses are different in your solution, you'll need to adapt it.

What ranges are you using ?

 

[Fluff]

Both formulae work.

Book1
	A	B	C
1	Dates	Fluff	Gerald
2	20/08/2019
3	21/08/2019
4	22/08/2019
5	23/08/2019	1	1
6	24/08/2019
7	25/08/2019
8	26/08/2019
9	27/08/2019
10	28/08/2019
11	29/08/2019
12	30/08/2019	2	2
13	31/08/2019
14	01/09/2019
15	02/09/2019
16	03/09/2019
17	04/09/2019
18	05/09/2019
19	06/09/2019	3	3
Test

Cell Formulas
Range	Formula
B2	=IF(WEEKDAY(A2)<>6,"",COUNTIF(B$1:B1,">=0")+1)
C2	=IF(WEEKDAY(A2)=6,MAX(B$1:B1)+1,"")

Judging by post#6 you entered the formula in B3 & filled down, not B2
Is your first date in A2?

 

[lezawang]

Yes it does work now. Thank you so much.

 

[Fluff]

Glad we could help & thanks for the feedback