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. I am not saying this is the source of the problem but certainly not helping :) — simpLE MAn
– simpLE MAn, Commented Mar 6, 2014 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
– user1759942, Commented Mar 6, 2014 at 15:55
The formula bar reads 1/2/1900 1:09:25 PM, but the formatted text is 61:09:25. — Jeff Maner
– Jeff Maner, Commented Mar 6, 2014 at 16:06

Community · Accepted Answer · 2017-05-23 12:28:44Z

1

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....

edited May 23, 2017 at 12:28

CommunityBot

11 silver badge

answered Mar 6, 2014 at 17:10

user1759942

1,3466 gold badges14 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeff Maner Over a year ago

That is obscure! Thanks!

user1759942 Over a year ago

your welcome, its totaly obscure! I've no idea why excel hasnt fixed a bug like that yet but yea.

Collectives™ on Stack Overflow

Excel Passing Wrong Date Value to VBA Function

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related