Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Date Validation check on excel

TheBIguy
7 - Meteor

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
         
         
         
         
1zN/AN/AN/A5.81N/AN/AN/A17.94         
2pN/AN/AN/A5.81N/AN/AN/A17.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
         
         
4xN/AN/AN/A26.47N/AN/AN/A56.99         
5yN/AN/AN/A26.47N/AN/AN/A57.00         
5 REPLIES 5
Sebastiaandb
12 - Quasar

@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 :-).

TheBIguy
7 - Meteor

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
7 - Meteor

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.

Sebastiaandb
12 - Quasar

@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 

DawnDuong
13 - Pulsar
13 - Pulsar

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.

Labels