Date Validation check on excel

Question

Hello All,

I need a small help in Validation check on excel. I have multiple rows like this where I have to check a particular date in a cell

So this date would be the "From Date" only which needs check the "To date" is perfect.

So the date has to MM/DD/YYYY and always To Date would be 1st of the month.

So now the validation check should pick the cell which is coming incorrect.

For ex. 6th cell of 3rd row have issue it has date as 31st , So this whole row should be picked up with row number.

Row No.

Fund Name3 Year
From/To
09/01/2018
08/31/2021
5 Year
From/To
09/01/2016
08/31/202110 Year
From/To
09/01/2011
08/31/2021Inception
To

08/31/20213 Year
From/To
09/01/2018
08/31/20215 Year
From/To
09/01/2016
08/31/202110 Year
From/To
09/01/2011
08/31/2021Inception
To

08/31/2021                                    1zN/AN/AN/A5.81N/AN/AN/A17.94         2pN/AN/AN/A5.81N/AN/AN/A17.97         3

Fund Name3 Year
From/To
09/01/2018
08/31/2021
5 Year
From/To
09/01/2016
08/31/202110 Year
From/To
09/01/2011
08/31/2021Inception
To

08/31/20213 Year
From/To
09/31/2018
08/31/20215 Year
From/To
09/01/2016
08/31/202110 Year
From/To
09/01/2011
08/31/2021Inception
To

08/31/2021                  4xN/AN/AN/A26.47N/AN/AN/A56.99         5yN/AN/AN/A26.47N/AN/AN/A57.00

DawnDuong · Answer

hi @TheBIguy

One way is to solve is by the Regex Tool. (see full document help at https://help.alteryx.com/20213/designer/regex-tool)

- If you only want to detect if the cell content is of a certain pattern e.g. in this case, Regex can be used under Match mode to detect something like this (.*From/To\s\d\d/01/\d\d\s\d\d/\d\d/\d\d.*)

- Check out the Parse and Tokenise modes as well.

Dawn.

Sebastiaandb · Answer

@TheBIguy I get what you want :-)! Thanks for the explanation. Do you have the sample Excel available? It's kind of a pain in the *** to copy this as it would make multiple rows for all the big multi row cells in either Excel or Alteryx.

Greetings,

Seb

TheBIguy · Answer

Hello @Sebastiaandb

My From date has to be 1st of the month, many times it comes as 31st so this cell I have to manually change.

So From Date has to be MM/01/YYYY and To date would be MM/31/YYYY

Basically I have a check to do from last month last date to all other MM/YYYY and 1st of that month

so 3rd row 6th Cell has "09/31/2018" while it has to be "09/01/2018" SO this 6th Row we need as comment that needs to be checked.