Solved Date functions: subtraction (2 Viewers)

[mishash]

I am trying to build button-click dates' VBA filter and seem to miss some logic here.
- How come the MONTH function in MONTH(DATE())-1 gives 0, but in DateSerial(YEAR(DATE()), MONTH(DATE())-1, DAY(DATE()) gives 12?
- In case of DateSerial(YEAR(DATE()), MONTH(DATE())-1, DAY(DATE()) the result is 2020/12/4. Why the MONTH subtraction affects the YEAR(DATE()) outcome as well?

 

[plog]

In short, that's how DateSerial() is intended to work. It was written to handle Month values outside 1-12 and day values outside 1-31.

1. MONTH(DATE(2020))-1 gives 0

That should throw an error, Date() takes no arguments, so putting 2020 in its parentheis will break it. However Month(Date()) -1 =0 because the current month is 1 and when you subtact 1 from 1 you get 0.

2. DateSerial(YEAR(DATE()), MONTH(DATE())-1, DAY(DATE()) gives 12

Month(Date()) still returns 1, then you subtract 1 from it and you get 0. So now that 0 is inside DateSerial and it treats 0 as 12. You haven't found an incosistency with Month(), you've found how DateSerial() effectively handles what could be error values.

Here's the documentation:

DateSerial function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

 

[moke123]

I wonder if its the same as the 0 day of the month which is the last day of prior month.

Playing around with it using -1,-2,+1, etc. gives the expected results.

?DateSerial(YEAR(DATE)+1, MONTH(date)-1, DAY(DATE) )
12/4/2021

?DateSerial(YEAR(DATE), MONTH(date)-1, DAY(DATE) )
12/4/2020

?DateSerial(YEAR(DATE), MONTH(date)+1, DAY(DATE) )
2/4/2021

?DateSerial(YEAR(DATE), MONTH(date)-3, DAY(DATE) )
10/4/2020

?DateSerial(YEAR(DATE), MONTH(date), DAY(DATE)-14 )
12/21/2020

?DateSerial(YEAR(DATE)-1, MONTH(date)+24, DAY(DATE)-365 )
1/4/2021

 

[mishash]

In short, that's how DateSerial() is intended to work. It was written to handle Month values outside 1-12 and day values outside 1-31.

1. MONTH(DATE(2020))-1 gives 0

That should throw an error, Date() takes no arguments, so putting 2020 in its parentheis will break it. However Month(Date()) -1 =0 because the current month is 1 and when you subtact 1 from 1 you get 0.

2. DateSerial(YEAR(DATE()), MONTH(DATE())-1, DAY(DATE()) gives 12

Month(Date()) still returns 1, then you subtract 1 from it and you get 0. So now that 0 is inside DateSerial and it treats 0 as 12. You haven't found an incosistency with Month(), you've found how DateSerial() effectively handles what could be error values.

Here's the documentation:

DateSerial function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

In short, that's how DateSerial() is intended to work. It was written to handle Month values outside 1-12 and day values outside 1-31.

1. MONTH(DATE(2020))-1 gives 0

That should throw an error, Date() takes no arguments, so putting 2020 in its parentheis will break it. However Month(Date()) -1 =0 because the current month is 1 and when you subtact 1 from 1 you get 0.

2. DateSerial(YEAR(DATE()), MONTH(DATE())-1, DAY(DATE()) gives 12

Month(Date()) still returns 1, then you subtract 1 from it and you get 0. So now that 0 is inside DateSerial and it treats 0 as 12. You haven't found an incosistency with Month(), you've found how DateSerial() effectively handles what could be error values.

Here's the documentation:

DateSerial function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

I left 2020 by mistake, of course. It should have been MONTH(DATE())-1 gives 0.
Thank you!