Affix Date Field with airSlate SignNow

Affix date field

[Music] alteryx is actually capable of doing calculations with dates what does this mean it means that if you were to ask alteryx how many days happened between august 1st and september 15th it would be able to calculate that exact number of days knowing that august ends at the 31st day and then continuing 15 days into september additionally if you would start it at august 30th and you said add two days it would know that that should be september first it even can keep track of leap years and it knows whether or not there is a february 29th in a given year or not this can be really useful for you if you work with data that contains dates and you need to perform calculations with those dates we're going to take a look at how you need to make sure your data are formatted to be able to be dates and how you can convert them into dates if they aren't already dates it's not unusual to have dates in many different formats when you're working with different data sets here just as an example i have three different possible layouts that you might receive for dates in your data an alteryx can handle every single one of these and more the first thing that's most important to understand about alteryx is that when it is working with dates as native dates it needs to have that date be a date time data type and in order for that field to correctly be a date time data field it needs to be in the standard alteryx date format so what does that look like for alteryx it is a four digit year a hyphen a two digit month a hyphen and a two digit date there's also a standard formatting if you want to include time but we're just going to focus on dates here so what happens when you bring in different dates like this well let's drag a select tool onto our canvas and see when we bring in our select tool if we run this to make it easier to look at we can see that the incoming date that is formatted already like our standard date time format it is easy for alteryx to recognize this as a date if this had come in as a string and we were to run this just like a string here you would still be able to just select date and convert this to a date and have no problems afterward by simply selecting in our select tool that this should be a date and it would be able to convert that to a date now if we look at our others if we try to force these to be dates just as they are you're going to see something happen which is because these are not in the standard format that alteryx is expecting when it gets states it is going to be unable to actually calculate what date these strings represent so as a reminder null here means missing unknown because alteryx was unable to convert these to dates so it had no idea what to put in these cells so what can we do besides just using the select tool to convert into dates well alteryx has a very useful tool in the parse category called the date time tool and we can drag this date time tool onto the canvas and we see that we have this option to convert a string into a standard date time format and all we have to do is select which string field we would like to convert and then once we've selected that string field all we have to do is provide the format that this date is in so that it knows how to interpret this string and put it out as a standard date so in this case we need to find one that has the full month name the day a comma and a year so we can look through all of the formats that we might have here and be able to select them so i actually see right here we have a full month day comma and a four digit year you also see that we have a few other options like mm mm means a two digit date a month and we actually have a word so we're going to select that here so we can run this and see did we successfully parse this if we had picked the wrong format let's say this one then again we would get nulls because we would have told alteryx to interpret the string incorrectly please do note that everything must match down to the punctuation the white space and everything else alteryx is able to adapt if you only have one digit for a day instead of zero three it can handle that but if this were no comma you would have to specify your own way what kind of output you should be getting so now that we can see that we can do the date time parse and how easy that is to be able to successfully convert what are your other options you may notice that the date time parse tool can only process a single date at a time so you may end up having to use multiple date time parse tools in a row there is nothing wrong with this you are absolutely welcome to use multiple date time parse tools in a row however if you are interested in leveling up your date time calculations the next option that you can do is to use a formula tool and in the formula tool we can actually find a function under date time that is called date time parse and with this date time parse field what we do is we can call a date field that we are interested in parsing so if we look at date two and then we can actually specify ourselves exactly the format that this string is and alters can parse it now how do you know how to specify this at first you may think i will just head back to this tool and i'll copy the specifications from here but unfortunately it doesn't quite work that way instead what you can do is you can refer to what are called date time specifiers and you can get this link here in our documentation or head to the help documentation and just find the date time functions under date time functions you can see these specifiers listed out and they're defined very carefully and you can see how the capitalization does matter and it usually indicates a difference between an abbreviation and the full word although in the case of m a lowercase m refers to a month digit and an uppercase m refers to minutes in time so we need to look for the specifications that we need in order to write our formula well we know that our date formula starts with the full month name so we're going to need the specifier capital b in order to show that this is a specifier every single one of them is preceded by an x by a percentage mark here a percentage sign that percentage sign indicates that this is in fact a specifier and isn't just the letter b so we know that we need a capital b to specify our month we next need to determine the day of the month so we can see here that the specifier lowercase d specifies one or two digits for our day so that's the second one we need then we need a four digit year so we can scroll down to year and we see that lowercase y is two digits and uppercase y is four digits so we have all the specifiers that we need so what we can do to set our parsing function here is we've already specified which field we would like to parse then we're going to simply do quotation marks and we'll list out our specifiers again making sure to exactly replicate the punctuation of this field so here we have the full month name specifier space then the day digit which is our lowercase d comma space capital y which specifies the four digit year and then we can name this column date to formatted now what's so great about this is that we can set this data type to date since that's what we are expecting and then we can add a second one and parse our next date so we could say date three formatted select date as our type and we could do a date time parse on date three and specify the next format here so here we have a two digit month which is a lowercase m we have a hyphen the day digit day and then hyphen and a two digit year which is lower case y and we can see that in one single fell swoop we are able to parse multiple date fields all in one go using multiple date time tools is perfectly acceptable and is a great way to get started with parsing your dates just remember that this format is for processing dates in alteryx if you need to format your dates for an output you can always convert them back to a string using the opposite option here date time format to string in which case when we select this what you're going to select is the format you would like it to write out something that has already been formatted as a standard date or you can use the function date time format which will take a standard formatted date and convert it into any string format that you would like so hopefully this gets you started with how to actually turn strings into functional dates that alteryx can use in native date calculations