Managing dates in SolveXia

The Date data type is notorious for being extremely fiddly and messy when it comes to data manipulation or data analysis. There's just no universally agreed-upon format for which dates should be stored and displayed. And so, careful attention is needed whenever you come across dates in your data.

Occasionally, you may need to link a SolveXia instruction to a Date data step property. And this is where things get tricky.

If you're not careful with the date formats, it may not behave as expected. For example, suppose that you would like to Copy rows selectively on a "Date" column, by linking to a "Date" data step property (below):

Note: that the date data step property is formatted specifically in the "dd-MMM-yyyy" format.

And so, if the dataset looks like this:

Now, even though the date correctly matches with the above date data step property (17/08/2018), the row will not copy over in the "Copy rows selectively" instruction.

This is because the date is formatted in the "mmm-yy" format (despite having the inherent value of 17/08/2018, as seen in the formula bar at the top).

And so, the way to resolve this is to either format the column appropriately or use a helper column that will explicitly re-format the column using Excel formulas.

1. You can format the column appropriately by selecting the entire column and formatting the cell to "Short Date":

2. Alternatively, you can create a helper column to simulate the re-formatting (this would be best if you would like to preserve the formatting of the original data), by using the TEXT formula:

This can be similarly applied to various other date-related processes in SolveXia. And so, be wary when it comes to dates!