Longtime member.....been years since I posted

[ken2step]

Cells A1:J1 contain dates formatted to display the year only.

Cell A2 I would like to enter a year.

Then I need a validation formula that would evaluate if that year is present in cells A1:J1

Would someone please help with this?

thanks,
Ken

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[cdwr0550]

try this

Dim current As String
Dim check As String

current = Format(DateTime.now, "YYYY")'''change
check = Range("B1").Text

If check = current Then

 

[ken2step]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I sure will...thanks! But status is same......Windows 10 - Excel 2010

 

[ken2step]

try this

Dim current As String
Dim check As String

current = Format(DateTime.now, "YYYY")'''change
check = Range("B1").Text

If check = current Then

Sorry don't know how to use VBA. Looking for a formula to use in Validation.

 

[Fluff]

How about

=IF(ISNA(MATCH(A2,YEAR(A1:J1),0)),"No", "Yes")

 

[ken2step]

How about

=IF(ISNA(MATCH(A2,YEAR(A1:J1),0)),"No", "Yes")

That did not work as the Year(A1:J1) returns values of 1900 for each cell value.

 

[Fluff]

In that case you do not have real dates in A1:J1, what is in those cells?

 

[ken2step]

In that case you do not have real dates in A1:J1, what is in those cells?

1-1-2021 formatted to display only the year. So unless the formatting is the problem the cells have good dates.

 

[Fluff]

Try Confirming the formula with Ctrl Shift Enter.