Varirables: ToDate() and FormatDate()
I cannot help it. I have an on again, off again love affair with variables. And this month it is on! For some reason, I've just enjoyed creating variables lately - using functions, entering extended calculation syntax, seeing that satisfying "your formula is correct" message when I get my syntax down properly on the first try...delightful.
Lately I've been getting questions on a variety of functions - using match(), previous(), todate(), formatdate(), if(), runningcount(), etc. The other day a colleague came to me with an issue she had. She was trying to use the ToDate() function, which was what she needed, but just wasn't getting it to work. It is a picky bugger.
First of all, let's differentiate between ToDate() and FormatDate(). ToDate() is a conversion function -- it takes a string and converts it to a date. This is useful if you have date data stored in the database as a string, but you want it to act like a date for formatting and sorting purposes. FormatDate() is used to control the display format of a date. However, it will return a string, so it won't sort like a date and you won't be able to use date functions with it unless you convert it back to a date with ToDate().
FormatDate()
Here is an example of when FormatDate() can be useful. Let's say you add a Last Refresh Date cell to your report.
Notice the last refresh date has a default format.
And because it is a date data type you can easily change the format using the format dropdown on the Numbers tab. Whohoo!
However, sometimes you might want to add a label to your last refresh date. In this example, we are concatenating "Last Refresh Date: " on to the refresh date value using the + sign. But notice that when you do that, you loses your selected date format and it defaults back to M/dd/yy.
This is because as soon as you concatenate text on to your LastExecutionDate() function the whole thing turns into a string. You can no longer use the formatting dropdown options on the Numbers tab.
The solution is to use the FormatDate() function to control the formatting as shown below.
If you aren't sure what the format strings are such as MM, Mmmm, dd, yy, etc. you can easily find them in the Web Intelligence help under "Custom Formats".
ToDate()
Let's move on to ToDate(). As mentioned earlier, this is useful when you have date data but it is stored in a string field.
In this example, Birth Date looks like a date but it is actually a string data type.
This makes it hard to apply a different display format and use other date functions with it, so we'll use ToDate() to convert it to a date. The ToDate() function has two parts -- the string you are converting to a date (generally the object name like [Birth Date]) and the date format the string is in (*NOT* the format in which you want the date to display). That is a common mistake.
=ToDate([Object name];"format string")
You need to tell the ToDate() function what each of the data values equate to (whether it is a month, a year, etc.). You do not want to provide it with the format you wish to output. You can do that later by formatting it or adding a FormatDate() function. I'll show you what I mean.
First, create a new variable.
Then create the formula, being sure the format you enter EXACTLY matches the format the data is in. For example, the birth date is in the format 1958-12-10 so it has the four-digit year, two-digit day, and two-digit month separated by dashes. That means the format is "yyyy-dd-MM", which is the format you'll provide to the ToDate() function so it knows how to correctly convert the data.
Once you convert something to a date you can just use regular date formatting to adjust the display format if you want. The Birth Date column that is a string doesn't allow you to format it but the new Birth Date - as date column is a date so you can format it using any of the built-in date formats or create your own custom date format.
Next, let's demonstrate how to combine the ToDate() and FormatDate() functions. In this example, let's say you want to concatenate the birth date and some text. You can start by putting together your text such as "Your birthday is: " and the ToDate() function.
But notice above that you've now lost the date formatting and since this column is a string you can't apply any date formatting using the dropdown on the Numbers tab. So, there is one more step. Did you guess it? Yep!! You need to wrap the date in a FormatDate() function, too. That way you can control the date appearance within a string.
Could you have just used FormatDate() along with the [Birth Date] object? The answer is "No" because the FormatDate() function requires something that has a date data type. Otherwise, you would have gotten a data type error when building your formula.
Remember [Birth Date] was a string, which is why it was necessary to first convert it to a date with ToDate(). Again, the final correct equation is:
="Your birthday is: " + FormatDate(ToDate([Birth Date];"yyyy-MM-dd");"Mmmm dd, yyyy").
Fun stuff, huh?
Dates are such a common thing to work it is extremely helpful to understand two of the most popular date functions. I'll continue the Variables series for awhile, as long as my love affair lasts. There are some more great date functions as well as some creative ways to take advantage of RunningCount(). I also have a great example for using the Previous function to roll up values from multiple lines on to one line. Later!
