WRITTEN BY: supportmymoto.com STAFF
Through the years, many posters on each SCN and BOB have been confused by methods to use these two useful features.
They’re comparable features, which might typically be used collectively to govern dates, however we’ll come to that later.
What does it do?
FormatDate means that you can cross in a date object and outputs a string within the format specified.
The fundamental assemble is =FormatDate([date object];”date format”)
I’ve a date object, Gross sales Date, that I need to show as a string within the format yr month – twenty fifth Jan 2016 ought to present as 2016-01
To attain this, I’d write =FormatDate([Sales Date];”yyyy-MM”)
The important thing factor to notice right here is the capitalisation of months – MM is used for months and mm is used for minutes. This is applicable to each FormatDate and ToDate.
What does it do?
ToDate means that you can cross a personality string or object and converts this to a date, primarily based within the enter masks offered
The fundamental assemble is =ToDate([string object];”enter format”)
I’ve been given a flat file enter and need to convert the date string object that represents a date to a date. An instance date string is 26062016 – twenty sixth June 2016
To attain this, I’d write =ToDate([date string];”ddMMyyyy”)
Observe that the enter format displays the format of the character string, not the date format that you really want the date to be displayed in when utilizing it. It is a widespread lure that That’s, if you would like the above date string to indicate as 06/26/16 in your report, you wouldn’t use “MM/dd/yyyy” as your enter format. Enter format is about the best way that the date string is structured; what you need to do is format your new date object utilizing the right-click, format quantity performance.
Mixed Utilization Instance – Getting the primary day of a given month
One easy option to get the primary day of the month is to mix the 2 features:
So what does that do? Nicely, working from the within, the very first thing achieved is to get the gross sales yr month – 201601 in our instance above – as a string. Then, we tag 01 on the top of it, to make it a date string 20160101. We then apply ToDate to that, specifying the right enter masks.
You could possibly then take this a step additional and use the RelativeDate to get the final day of the earlier month – on condition that not all months are the identical lengths, it’s simpler so as to add months primarily based on first days then work backwards.
The important thing take away from that is to do not forget that for ToDate, the format that you’re specifying pertains to the item you might be putting into the operate, not the specified output date. That is the alternative for FormatDate, the place you might be specifying the output format.
One other use for this performance has come about with the newest Webi launch, 4.2SP3, which permits us to merge variables with objects when working with a number of knowledge suppliers. This can make issues like evaluating budgets to gross sales simpler; budgets are sometimes for the month and gross sales by day. Now you possibly can format your gross sales date as a month, you possibly can merge this new variable along with your funds month and examine the 2 simply.