Monarch and Excel Make Your Data Work for You

Posted in June 12th, 2008

Yesterday I received a message from a reader asking about how she could resolve a challenge with extracting dates from her report.

The problem was that the date in the report appeared in the form of 01-Oct-07, and she wanted 10-01-2007. She’d been trying to use, correctly, the CtoD function, but kept getting null values no matter what she did.

I supposed that she was really close to a possible solution. If she was indeed using CtoD(RptDate,”m/d/y”), where RptDate is a character field, then Monarch would indeed return a null value. A subtle change to CtoD(RptDate,”d/m/y”) yields 10/1/2007.

The date is correct, but that’s not the exact format she wanted. To get that, with leading zeros for both the month and the day numbers, it takes a much more complex formula:

Right("00"+Trim(Str(Month(CtoD(RptDate,"d/m/y")))),2) + "-" + Right("00"+Trim(Str(Day(CtoD(RptDate,"d/m/y")))),2) + "-" + Trim(Str(Year(CtoD(RptDate,"d/m/y"))))

This formula uses the basic date conversion formula as above, but extracts out the day, month and year values, adds leading zeros with some string conversion and manipulation functions, and ties them all together with a couple of dashes.

Just a few minutes later, a member of Datawatch’s online Monarch Forum asked another date related question. In this case, the character date appeared as mmm-yyyy, and once again, CtoD was returning null values.

CtoD needs three values in order to convert to a date: day, month, and year. If you’re missing any of them, CtoD will fail in every case. This means that you’ve got to supply what’s missing yourself to make it work.

Since the report itself didn’t include a day value, then the day number that we supply the CtoD isn’t really important; it’s just an arbitrary value to make the function work. To make sure that your formula works for every month, you could pick the 15th or the 28th, but in situations like this you’re probably better off with choosing the first of the month.

A possible formula to resolve this challenge is as follows:

CtoD(Left(RptDate,3)+"/01/"+Right(RptDate,4),"m/d/y")

Again, string manipulation functions play a role as we pull off the left and right sides of the report date, and add our own day number in the middle, and the “m/d/y” tells Monarch what format to expect in our final string.

Today’s the Day

Monarch includes quite a number of date related functions, and we’re sure to explore them further in the future, but the thing to keep in mind is that sometimes they need a little help from you to get you the right results. Do some more homework today on Monarch’s functions and you’ll soon feel that you can excel with Monarch.

2 users commented in " Conquering Date Problems in Monarch "

Nick said,

in June 13th, 2008 at 5:52 am

Remember, you only need 2 characters for Month and Day, thus you only need to append one “0″, so you can modify your formula slightly by changing the “00″ appends to “0″. Also, shouldn’t CharDate be RptDate in the last part of the formula?

Also, concerning the last formula, those dates /usually/ indicate a fiscal date which is normally the end of the month, you can wrap the function in either the End_Of_Current_Month() formula, or use the DateAdjust() formula to return the end of the month date:

DateAdjust((CtoD(Left(RptDate,3)+”/01/”+Right(RptDate,4),”m/d/y”)),0,1)-1

Great work Sandy… I am still amazed how many ways we can excel with Monarch.

Sandy said,

in June 13th, 2008 at 8:51 am

Thanks for the input Nick. Of course, you’re correct: only one leading zero is required for that purpose. Good thing that two won’t cause a problem.

I think that the Hungarian in me typed the Char prefix; some kind of Pascal holdover. I’ll edit that one.

I’ve been working for the past while with oddball fiscal periods, wherein the last day of the period isn’t usually the last day of the calendar month. Thus my “standard” has been to use the 1st for reporting purposes. Thanks for pointing out some of the available options for more conventional periods, though.

If I didn’t know better, I’d say that *someone* has just volunteered to write the next post on date functions. Up for it?