Date vs DateSerial

Sunday 20 November 2016 @ 6:20 pm

There are many functions in Crystal with overlapping capabilities, like Val() and ToNumber(). I have always thought of Date and DateSerial as identical functions that both allow you to specify a literal date in a formula by specifying the year, month and day as numbers like this:

Date (2016,11,24)
DateSerial (2016,11,24)

The three numbers can either be literal values, fields or calculations like this:

Date ( {table.Year}, {table.Month} + 2 , 3 )

This will create a date value by taking the Year value from the table, adding two months to the Month value from the table and then taking the 3rd day of that month.

But I just discovered (via a post by AndyMc in Tek-Tips.com) an important difference between the two functions, which is how each function would respond if the value in the field {table.month} was 12.  The Date() function would generate an error because 12 + 2 = 14, which is an invalid month.  The Date() function requires that the month number be from 1 to 12. Also, the day number has to be between 1 and 31 and has to be a number valid within that month.

But the DateSerial() function doesn’t have this limitation. If the formula above used DateSerial(), adding 2 months to month 12 would automatically move forward to next February.  You can also give DateSerial() a day number that is more than 31 and it will move automatically to the next month.

So the The following expression would return 5/1/2016:

DateSerial (2016, 4, 31)

But using Date() would generate an error:

Date (2016, 4, 31)

 

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)







Leave a Reply

Recrystallize Pro