Crystal Reports: Days360 Function

	Resources
	Best sellers: cView Report Analyzer cViewSERVER ReCrystallize

	Articles: Administration Advanced Basic Crystal eNL Database Financial Problems Solved Books: CR Books Database Books Developer Books
	Tools: Analyzers Bestsellers CR Schedulers CR UFLs CR Viewers DataBase Tools Graphics International Mail UFLs ReCrystallizePro
	Add'l: About us Contact Us cViewSUITE Ppt Support
	CrystalReports on Steroids

Crystal Reports: Days360

Every now and then we discover a function we haven’t seen before. The Day360 function is new to Crystal Reports 2008 and calculates the difference between two dates using a 360 day year and a 30 day month.

If the two dates are in the same month and year then the value from subtracting the two dates, or from using this function will be the same.

However if the dates are in different months or years, the Days360 function will normalize the values and calculate a 30 day month and a 360 day year. This will help smooth out the varying lengths of each month if you are needing to do some performance comparison over a long time period.

The results of the Days360 function are as follows:

Days360(Date(2009,1,1), Date(2009,3,1))

Returns a result of 60 when in fact there are only 59 days between those two dates.

This function returns the same result as the same function in Excel.

Well, this sounds really good.

So if you then went looking for the Days 360 function, where could you find it? You might expect it to be listed as a Date/Time function.

Sorry, but that isn’t where it is. For some reason, it’s listed under the “Financial” functions in the function tree.

It is also available only in Crystal Reports 2008.

If you ever have trouble looking for a function, remember the search toolbar (binoculars) which will highlight where the function is listed.

Click Image to enlarge.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.

A related issue is how you format dates. A common problem is the backwards, unclear date formatting often used in the United States. In the United States, many companies use the format MM/DD/YYYY. For example, they write 09/11/2001 and say "September 9, 2001." And it's become popular in the USA to refer to this date as "911."

What's interesting about this is the airlines use a different format. They use DD/MM/YYY. So does the military. So does most of the rest of the world.

Suppose you are communicating with someone who does business internationally and ask for an item to be delivered "on 03/09/2006." When can you expect that item to arrive? You may be thinking you are going to get it on March 9th. But, the other person reads this as 03 September.

What can you do to avoid problems? A commonly accepted solution is to use MMM to indicate the month. Three upper case letters in a date format means you are using a three-letter designation for the month. For example, 03SEP2006. There's no confusion as to what month you can expect delivery.

This method of mixing alpha and numeric solves a communication problem quite handily. But what about inside a database or spreadsheet, where you need to do calculations with dates? It's a bit hard to subtract FEB from NOV, now, isn't it? So how do you guarantee you are going to get the month and the day in the correct order?

In software--especially in databases, file systems, and spreadsheets, another format is fairly standard and it solves that problem. You'll also see this format in inventory systems and many other tabular style applications. That format is YYYY-MM-DD. A variation is YYYYMMDD.

So, it will look like this: 20060903. The largest unit (years) are on the left, and the smallest units (days) are on the right. Why not do it the other way? Well, you could. But what if you wanted to sort by date? Or perform a calculation that involved different months or years?

To avoid confusion:

Use the YYYYMMDD format when working with data.
Use the DDMMMYYYY format when working with information.
Don't use the MMDDYYYY format at all.

To avoid related confusion:

Always use four digits for the year, not two.
Always provide a legend for a date mask, when you are forcing the format for data validation purposes.
When providing a legend for a date mask, remember that lower case letters indicate hours, minutes, and seconds: YYYYMMDDhhmmss.

If you are using the 12-hour clock, you should consider going to a 24-hour time standard. This also avoids confusion, especially if you run a multishift or 24-hour operation.