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 Name | 3 Year From/To 09/01/2018 08/31/2021 | 5 Year From/To 09/01/2016 08/31/2021 | 10 Year From/To 09/01/2011 08/31/2021 | Inception To 08/31/2021 | 3 Year From/To 09/01/2018 08/31/2021 | 5 Year From/To 09/01/2016 08/31/2021 | 10 Year From/To 09/01/2011 08/31/2021 | Inception To 08/31/2021 | |||||||||
1 | z | N/A | N/A | N/A | 5.81 | N/A | N/A | N/A | 17.94 | |||||||||
2 | p | N/A | N/A | N/A | 5.81 | N/A | N/A | N/A | 17.97 | |||||||||
3 | Fund Name | 3 Year From/To 09/01/2018 08/31/2021 | 5 Year From/To 09/01/2016 08/31/2021 | 10 Year From/To 09/01/2011 08/31/2021 | Inception To 08/31/2021 | 3 Year From/To 09/31/2018 08/31/2021 | 5 Year From/To 09/01/2016 08/31/2021 | 10 Year From/To 09/01/2011 08/31/2021 | Inception To 08/31/2021 | |||||||||
4 | x | N/A | N/A | N/A | 26.47 | N/A | N/A | N/A | 56.99 | |||||||||
5 | y | N/A | N/A | N/A | 26.47 | N/A | N/A | N/A | 57.00 |
@TheBIguy Hi! I think i can help you out. Only problem is, i'm not exactly sure about what's wrong with your example. In the 6th cell of the 3rd row i see "Inception To 08/31/2021", so it has only one date instead of two, is that a problem? So what do you want to check:
- If there are always 2 dates (a From and To date);
- If these dates are always the first of the month or the last of the month;
- If both dates are in the correct formatting (MM/DD/YYYY)
Let me know! Technically, it's all doable :-).
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.
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.
@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
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.