Mark the first row in a series of dates

[DarkoDeign2]

I have dates in col B.
In col E I would like to have a formula to indicate the first row of the same dates, as shown in the example below. How can I do this in the easiest way?

Book2
	B			E
2	Date			Status
3	2023-07-26			First
4	2023-07-26
5	2023-07-26
6	2023-07-26
7
8	2023-08-02			First
9	2023-08-02
10	2023-08-02
11
12	2023-08-10			First
13	2023-08-10
14	2023-08-10
15	2023-08-10
16	2023-08-10
Sheet1

Cell Formulas
Range	Formula
B3:B7,B11	B3	=IFERROR(IF('[Agenda & Minutes (2).xlsx]Agenda'!B3="","",'[Agenda & Minutes (2).xlsx]Agenda'!B3),"Error")

 

[Anonymous1378]

Try a COUNTIF()=1? I'm assuming the same dates won't be found further down the range.

Book66
	B			E
1	Date			Status
2	26-07-23			First
3	26-07-23
4	26-07-23
5	26-07-23
6
7	02-08-23			First
8	02-08-23
9	02-08-23
10
11	10-08-23			First
12	10-08-23
13	10-08-23
14	10-08-23
15	10-08-23
Sheet1

Cell Formulas
Range	Formula
E2:E15	E2	=IF(COUNTIF(B$2:B2,B2)=1,"First","")

Or perhaps an ISNUMBER()

Book66
	B			E
1	Date			Status
2	26-07-23			First
3	26-07-23
4	26-07-23
5	26-07-23
6
7	02-08-23			First
8	02-08-23
9	02-08-23
10
11	10-08-23			First
12	10-08-23
13	10-08-23
14	10-08-23
15	10-08-23
Sheet1

Cell Formulas
Range	Formula
E2:E15	E2	=IF(ISNUMBER(B1),"","First")

 

[DarkoDeign2]

Hi,
When I try this I get "First" on all rows, I think this is because the dates are not typed manually, but pulled from another sheet with a formula.

 

[Anonymous1378]

I'm assuming you tried the first method and omitted the $ sign?

 

[DarkoDeign2]

Hi,

I have tried both versions but I still think that it might be the dates in col B that is messing this up, since these dates are pulled from another sheet using a formula..

Book3
	B			E	F
1	Date			Countif	ISNumber
2	2023-07-26			First	First
3	2023-07-26			First
4	2023-07-26			First	First
5	2023-07-26			First	First
6				First	First
7	2023-08-02			First	First
8	2023-08-02			First
9	2023-08-02			First
10	2023-08-02			First
11	2023-08-02			First
12	2023-08-02			First
13
14					First
Sheet1

Cell Formulas
Range	Formula
E2:E14	E2	=IF(COUNTIF(B$2:B2,B2)=1,"First","")
F2:F14	F2	=IF(ISNUMBER(B1),"","First")
B2:B14	B2	='[Agenda & Minutes (2).xlsx]Test (4)'!$B3

 

[Anonymous1378]

My guess as to why countif() is behaving that way is that you have date/time underlying values which isn't coming across in XL2BB?
I can't tell why ISNUMBER() isn't working if that is the case, however. Are your underlying values part text and part numbers by any chance?

 

[DarkoDeign2]

Col B is only dates

 

[MARK858]

@DarkoDeign2 what does the formula =ISNUMBER(B2) dragged down return? are they all true?

 

[DarkoDeign2]

It works better after I got some help with the formulas in Col B.
B3 is the only cell with an actual date, the rest of the cells in B shows a date that is calculated.
Countif seems to count also the blank rows. Isnumber seems to work a little better, but here all the empty rows belows the rows with data shows First...

Book3
	B			E	F
1	Date			Countif	ISNumber
2	2023-07-26			First	First
3	2023-07-26
4	2023-07-26
5	2023-07-26
6				First
7	2023-08-02			First	First
8
9					First
10					First
11					First
12					First
Sheet1

Cell Formulas
Range	Formula
E2:E12	E2	=IF(COUNTIF(B$2:B2,B2)=1,"First","")
F2:F12	F2	=IF(ISNUMBER(B1),"","First")
B3:B12	B3	=IF(D3="","",LOOKUP(9^9,B$2:B2)+7*(B2=""))

 

[Phuoc]

Try this:

Book1
	B			E
1	Date			Status
2	7/26/2023			First
3	7/26/2023
4	7/26/2023
5	7/26/2023
6
7
8	8/2/2023			First
9	8/2/2023
10
11	8/10/2023			First
12	8/10/2023
13	8/10/2023
14	8/10/2023
15	8/10/2023
Sheet1

Cell Formulas
Range	Formula
E2:E15	E2	=IF(AND(N(B1)=0,ISNUMBER(B2)),"First","")