1

I have noticed something strange. Let's say I have a function:

Function ds(l As Long) As String
  ds = DateSerial(Year(l), Month(l), Day(l))
End Function

When I type ds(date) I will get string representing current date. If I type ds(1) it returns 31/12/1899. ds(-1) returns 29/12/1899. And then, ds(0) brings: 00:00:00. One could expect 30/12/1899, coudn't he? Well, it is not a matter of life and death, but I am curious, why DateSerial behave this way?

Community
  • 1
  • 1
MarcinSzaleniec
  • 2,246
  • 1
  • 7
  • 22

1 Answers1

1

Short answer:

0 is really a tough value to convert to date. Thus, it is skipped. Otherwise it would have broken lots of business logic in programs. This is my personal finding with 0 in VBA - Why is 0 divided by 0 throwing an overflow error in VBA?

Longer answer:

In general, 1 is the first date value in VBA and Excel.

In VBA, 1 is represented as 31.12.1899 In Excel it is represented as 01.01.1900 or 01.01.1904, depending on whether you have enabled the 1904 Date system in Excel. MSDN 1904 date system.

The good thing is that -1 (and all negative values) cannot be represented as dates in Excel, as it gives an error, thus there is one less problem:

enter image description here

Easiest answer:

DateSerial accepts arguments between 100 and 9999 for the year, thus -1 is outside their range - DateSerial MSDN. Year() accepts from 1 to 9999 correspondingly, thus -1 and 0 are also outside the boundaries. Year MSDN

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • Thank you for your reply. But, anyway, argument -1 don't cause error in my little function. Only 0 returns unexpected result. – MarcinSzaleniec Nov 24 '17 at 14:16
  • @MarcinSzaleniec - that is because `DateSerial(1899,12,30)` is evaluated to `0` and there is no way to present it as a date. Thus, it gives `00:00:00`. See this by writing `?clng(DateSerial(1899,12,30))` in the immediate window. – Vityata Nov 24 '17 at 14:23