I have a column sg1 with date as string and may have some extra characters like
25/10/18 (M)
25/06/15 (P) this is planned.
My date format is DD/MM/YY. I have to colour the cells based on cell value. I have written the background colour expression for the column. The last condition is not working and throwing error.
=IIF(Fields!sg1.Value = "W","Khaki",
IIF(Fields!sg1.Value="g","Gray",
IIF(Fields!sg1.Value="b","Blue",
IIF((LEN(Fields!sg1.Value)=12 And
Format(LEFT(Fields!sg1.Value,8),"YY-MM-DD")<Today),"Pink",
"Green"))))
I am not able to get what is the issue.
This is more complex then I thought. I tested it myself and this way it should work:
=IIF(Fields!sg1.Value = "W","Khaki",
IIF(Fields!sg1.Value="g","Gray",
IIF(Fields!sg1.Value="b","Blue",
IIF((LEN(Fields!sg1.Value)=12 And
DateSerial(Mid(Fields!sg1.Value, 7, 2),
Mid(Fields!sg1.Value, 4, 2),
Left(Fields!sg1.Value, 2))
<Today),"Pink", "Green"))))
Where from 25/10/18
is Mid(Fields!sg1.Value, 7, 2) = 18 (YY)
, Mid(Fields!sg1.Value, 4, 2) = 10 (MM)
and Left(Fields!sg1.Value, 2) = 18 (DD)
.