Problem with Excel 2010: 1900 vs 1904 date system and VBA

[jmwbowen]

**Windows 7, Excel 2010**

I have a spreadsheet with one sheet set up as a userform to enter records and navigate records with button macros.

In cell (name: DT_Formdate) user enters date and Worksheet_Change event puts that date in the appropriate row in another hidden sheet containing all the records (set up like single-table database).

There are two buttons that allow user to go forward and backward through the records and the userform is updated accordingly.

Sub NextDTRecord()
If [DT_RecordNumber] < Data.Range("tblDT_Data").Rows.Count Then
    [DT_RecordNumber] = [DT_RecordNumber].Value + 1
    Call UpdateDisplayedRecord
    [DT_FormHandler].Select
End If
End Sub

Sub PreviousDTRecord()
If [DT_RecordNumber] > 1 Then
    [DT_RecordNumber] = [DT_RecordNumber].Value - 1
    Call UpdateDisplayedRecord
    [DT_FormHandler].Select
End If
End Sub

Sub UpdateDisplayedRecord()
[DT_FormDate] = [DT_TrainingDate].Offset([DT_RecordNumber].Value, 0)
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Set ws = DetectionTraining
Select Case Target.Address
 
Case "$H$6"         'Change to Training Date
    [DT_TrainingDate].Offset([DT_RecordNumber].Value, 0) = [DT_FormDate]

End Select
End Sub

'Code has been simplified to just the relevant data.

Now, if I set the Excel Options to Date System 1900, then I have no problems entering dates, navigating through records and dates changing accordingly, and changing dates.

But, with the Date System set to 1904, I have problems. Here is a chronological example of what I might do and what happens.

Enter Excel Options, set Date System to 1904

Use navigation buttons to go to record #1
Enter "5/9/1980" into date field
The corresponding record in the other sheet shows "5/10/1984"!!! (I'm using Excel Table to hold data)

Navigate to record #2
Enter "8/27/2012" into date field
The corresponding record in the other sheet shows "8/28/2016"!!!

Use navigation button to go back to record #1
Now the date field (which should have held "5/9/1980" for record #1 shows "5/11/1988"!!! (it increases by 4 years 1 day each time the cell is accessed.)

Go back to record #2
The user sheet and data sheet now both show "8/29/2020" where "8/27/2012" were entered!!!


Okay, keep in mind that there are NO problems when using the 1900 date system.

Any ideas? The only macros making changes to these cells are listed above.

 

[MARK858]

It makes perfect sense a date is just a number starting at 1 in whichever year format for instance today in 1900 format is 41149(formatted as date) but 41149 from Jan 1st 1904 is 29th August 2016.
Basically it will always be a difference of 1462 days.

I think you will need to choose a format and stick to it or obviously deduct 1462 days from the 1904 result

 

[jmwbowen]

Thanks for replying.

I understand how the serial number works for the two different date systems, but that doesn't explain the actual problem.

If I permanently choose the 1904 date system and enter 5/9/80 into a cell, the macro will then enter that value into another cell on another sheet. Then when that record is pulled later on, the user sheet should again show 5/9/80. This doesn't happen.

Using the above macros to navigate back and forth between records ADDS 4 years 1 day to the date everytime the cell is accessed. Looking at the code, there is no reason to believe this should happen.

Specifically, the instant I enter 5/9/80 into $H$6, that same value should be entered into another cell on another sheet. What really happens is that 5/10/1984 is entered.


It almost looks like $H$6 is recognizing the 1900 date system while the other sheet is seeing the 1904 date system. This shouldn't be possible as the date system for the ENTIRE workbook should be the same.

 

[MARK858]

Ah, I understand the problem now. Was the workbook created on a different operating system i.e. Windows but you are now on a Mac?
Or more accurately was the sheet pasted from a Windows sheet to a Mac?

 

[jmwbowen]

I am running Windows 7 on Parallels on a Mac, but the file itself has never seen the Mac side, only the Windows side, so there's no issues there. I know the Mac defaults to 1900 system while Windows defaults to 1904 system.

I could always just set this particular workbook to the 1900 date system and avoid this problem altogether, but the simple fact that this is happening perplexes me. I'm trying to figure out the cause.

That brings up another question.

If I can't figure out this problem, my only option is to set the workbook to the 1900 date system (where the VBA works properly). This workbook is not meant for me, its meant for other users on their computers. Their Excel application will most likely be set by default to the 1904 date system. If they open this workbook (set to 1900 date system), will Excel try and change the date setting back to 1904 (thereby messing up all the dates) or leave it the way it is and adjust accordingly?

 

[MARK858]

Not having a Mac here to do any tests on I am afraid I can't answer the last question but I suspect it will.
The reason I asked the question in my last post is I could remember something similar happening on another forum where martindwilson gave the reply below which isn't going to help much in your situation.
Sorry I can't be of more help.

Re: Dates change when cut and pasted

definately something to do with the 1904 date system mac uses as default
from microsoft


How to Transfer Files Between Excel for the Macintosh and Excel for Windows
By default, Excel for the Macintosh uses the 1904 date system, and Excel for Windows uses the 1900 date system. This means that when you type the serial number 1 in Excel for the Macintosh and format it as a date, Excel displays it as 1/2/1904 12:00 a.m. Excel for Windows displays the serial number 1 as 1/1/1900 12:00 a.m. If you transfer files from Excel for the Macintosh to Excel for Windows, this difference in the date systems should not cause a problem, because the date system is stored in each file. However, if you copy and paste between files with different date systems that originated on different platforms, dates may be displayed four years and one day away from their correct date.




​

In Microsoft Excel for Mac, you can change to the 1900 date system by clicking Preferences (on the Edit menu in Excel 2001 for Mac, or on the Excel menu in later versions), clicking the Calculation tab, and then clicking to clear the 1904 date system check box. In Excel for Windows, you can change to the 1904 date system by clicking Options on the Tools menu, clicking the Calculation tab, and then clicking to select the 1904 date system check box.

.

 

[jmwbowen]

Ahhh, for some reason, I was remembering it backwards. Well, this will work for me then since windows defaults to the 1900 date system, as I was not having any issues with the 1900 date system.

Thanks sir.