Over the years, many posters on both SCN and BOB have been confused by how to use these two handy functions.
They are similar functions, which can often be used together to manipulate dates, but we’ll come to that later.
What does it do?
FormatDate allows you to pass in a date object and outputs a string in the format specified.
The basic construct is =FormatDate([date object];”date format”)
I have a date object, Sales Date, that I want to display as a string in the format year month – 25th Jan 2016 should show as 2016-01
To achieve this, I’d write =FormatDate([Sales Date];”yyyy-MM”)
The key thing to note here is the capitalisation of months – MM is used for months and mm is used for minutes. This applies to both FormatDate and ToDate.
What does it do?
ToDate allows you to pass a character string or object and converts this to a date, based in the input mask provided
The basic construct is =ToDate([string object];”input format”)
I have been given a flat file input and want to convert the date string object that represents a date to a date. An example date string is 26062016 – 26th June 2016
To achieve this, I’d write =ToDate([date string];”ddMMyyyy”)
Note that the input format reflects the format of the character string, not the date format that you want the date to be displayed in when using it. This is a common trap that That is, if you want the above date string to show as 06/26/16 in your report, you would not use “MM/dd/yyyy” as your input format. Input format is about the way that the date string is structured; what you should do is format your new date object using the right-click, format number functionality.
Combined Usage Example – Getting the first day of a given month
One simple way to get the first day of the month is to combine the two functions:
So what does that do? Well, working from the inside, the first thing done is to get the sales year month – 201601 in our example above – as a string. Then, we tag 01 on the end of it, to make it a date string 20160101. We then apply ToDate to that, specifying the correct input mask.
You could then take this a step further and use the RelativeDate to get the last day of the previous month – given that not all months are the same lengths, it’s easier to add months based on first days then work backwards.
The key take away from this is to remember that for ToDate, the format that you are specifying relates to the object you are placing into the function, not the desired output date. This is the opposite for FormatDate, where you are specifying the output format.
Another use for this functionality has come about with the latest Webi release, 4.2SP3, which allows us to merge variables with objects when working with multiple data providers. This will make things like comparing budgets to sales easier; budgets are typically for the month and sales by day. Now you can format your sales date as a month, you can merge this new variable with your budget month and compare the two easily.