PDA

View Full Version : Match 1 date against 3 col

Kal-El
05-30-2017, 08:41 AM
I have a Date in Cell A1 and I need to compare it against dates in Cell C1,D1,E1

If cel A1 is not the first day of the month then "Not Aligned"

If Cel A1 is the first of the month but the date in either C1,D1,E1 does not match then "Not Aligned"

IF If Cel A1 is the first of the month and the date in either C1,D1,E1IS the matching first day of the Month of A1 then "Aligned"

Any thoughts

mdmackillop
05-30-2017, 10:27 AM
=IF(AND(DAY(A1)=1,NOT(ISNA(MATCH(A1,C1:E1)))),"Aligned","Not Aligned")

Paul_Hossler
05-30-2017, 10:56 AM
I believe that these are the rules

Rule 1

A1 = 1/4/2017 -- Not aligned since not first day of month

Rule2

A1 = 1/1/2017
C1 = 1/2/2017, D1 = 2/3/2017, E1 = 3/4/2017 -- not aligned since A1 is first of month but A1 not = any of C1, D1, E1

Rule3

A1 = 1/1/2017
C1 = 1/2/2017, D1 = 2/3/2017, E1 = 1/1/2017 -- aligned since A1 = E1

I'm not sure that Rule3 is needed since if all of C1, D1, and E1 <> A1 fails, then at least one of the 3 must = A1

Option Explicit

Function Aligned(Date1 As Date, Date2 As Date, Date3 As Date, Date4 As Date) As String
Aligned = "Not Aligned"

If Day(Date1) <> 1 Then Exit Function

If Date1 <> Date2 And Date1 <> Date2 And Date1 <> Date4 Then Exit Function

Aligned = "Aligned"
End Function