Excel Passing Wrong Date Value to VBA Function

Question

I have a worksheet with Date values. Excel interprets them correctly as Dates.

I have a VBA function that accepts a Date parameter.

When I call this function from the worksheet, sometimes it works correctly, and sometimes it doesn't. When I set a breakpoint in the function to examine the value passed in to the function from a cell that is incorrect, I discover that the wrong date is passed to the function--it's a day off from what should be passed to the function. The time value is correct.

This occurs in random places on the worksheet. In fact, in one cell that it didn't work correctly in yesterday, it now works correctly today.

The time portion of the value doesn't seem to make a difference. It can be 22:00 hours, 08:00 hours, it's still off by a day.

Does this make any sense to anyone? I am seriously perplexed.

Edit:

I didn't post any code because I don't find it to be a code issue. But here's the function signature:

Function timeToDecimal(time As Date, semanticTimeFormat As String) As Double

Setting a breakpoint on the first line of executable code in the function, I see that time is, for example, 1/1/1900 1:09:25 PM, when, on the worksheet it is clearly 1/2/1900 1:09:25 PM.

The issue isn't the VBA code I've written. The issue is Excel's passing of the data to the function. Is there some quirk I am unfamiliar with in the interface between Excel and VBA?

You should not use the word `time` for a variable name as it is a **[reserved keyword](http://msdn.microsoft.com/en-us/library/aa445227(v=vs.60).aspx)**. I am not saying this is the source of the problem but certainly not helping :) — simpLE MAn, Mar 06 '14 at 15:51
so in the worksheet, is the value ACTUALLY `1/2/1900 1:09:25 PM` or is it some other date format? like looking at the cell, it that the exact text of it? — user1759942, Mar 06 '14 at 15:55
The formula bar reads `1/2/1900 1:09:25 PM`, but the formatted text is `61:09:25`. — Jeff Maner, Mar 06 '14 at 16:06
post some of the other dates that are not working today if you would. I may have a lead on this and I need more examples :) — user1759942, Mar 06 '14 at 16:08
specifically if you could include the date in the cell that was wrong yesterday but correct today, that among others, would help :) — user1759942, Mar 06 '14 at 16:14
1/2/1900 10:38:07 PM, 1/2/1900 8:40:10 AM, 1/1/1900 2:07:21 AM — Jeff Maner, Mar 06 '14 at 16:18
Wrong yesterday, correct today: `3:54:49 PM`--the date is no longer there! — Jeff Maner, Mar 06 '14 at 16:19

score 1 · Accepted Answer · edited May 23 '17 at 12:28

IT WOULD APPEAR! that for some reason there is a bug in excel where 1990 is incorrectly interpreted as a leap year - mentioned here: How to represent a DateTime in Excel in the accepted answer. and I tested on a sheet of my own, putting the dates from 01/01/1900 up to 31/12/1901 and then i used vba like this:

Sub test()


Dim str As String
Dim test As String
Dim test2 As String

test2 = ActiveCell.FormulaR1C1
test = ActiveCell.Value



While Not ActiveCell.FormulaR1C1 = ""
    str = ActiveCell.Value
    ActiveCell.Offset(0, 2).Value = str
    ActiveCell.Offset(1, 0).Activate

Wend



End Sub

and from 01/01/1900 - 01/03/1900 thats where dates were off. the dates as a result of vba were all behind a day from what was in the spreadsheet:

01/01/1900 0:00     31/12/1899
02/01/1900 0:00     01/01/1900 0:00
03/01/1900 0:00     01/02/1900 0:00
04/01/1900 0:00     01/03/1900 0:00
05/01/1900 0:00     01/04/1900 0:00
06/01/1900 0:00     01/05/1900 0:00
07/01/1900 0:00     01/06/1900 0:00
08/01/1900 0:00     01/07/1900 0:00
09/01/1900 0:00     01/08/1900 0:00
10/01/1900 0:00     01/09/1900 0:00
11/01/1900 0:00     01/10/1900 0:00
12/01/1900 0:00     01/11/1900 0:00
13/01/1900 0:00 01/12/1900 0:00

but then starting 01/03/1990 the dates all lined up all the way to where i stopped at the end of 1901:

01/01/1901 0:00     01/01/1901 0:00         
02/01/1901 0:00     01/02/1901 0:00         
03/01/1901 0:00     01/03/1901 0:00         
04/01/1901 0:00     01/04/1901 0:00         
05/01/1901 0:00     01/05/1901 0:00         
06/01/1901 0:00     01/06/1901 0:00          
07/01/1901 0:00     01/07/1901 0:00         
08/01/1901 0:00     01/08/1901 0:00         
09/01/1901 0:00     01/09/1901 0:00         
10/01/1901 0:00     01/10/1901 0:00         
11/01/1901 0:00     01/11/1901 0:00         
12/01/1901 0:00     01/12/1901 0:00

SO! you'll likely need a part of the function to check the date and from 31/12/1899 to 28/02/1900 you'll need to move the day up 1.

*you'll notice the second set of dates shown, the right hand side, is all formatted mm/dd/yyyy - i have no idea why excel did that....

your welcome, its totaly obscure! I've no idea why excel hasnt fixed a bug like that yet but yea. — user1759942, Mar 06 '14 at 20:23

Excel Passing Wrong Date Value to VBA Function

1 Answers1

Linked