Dates

[johnboy]

hi all,
A small problem with date value in an addin spreadsheet.
I have a holiday calendar for my department in the form of a spreadsheet
which uses formulas to calculate dates for each month. This spreadsheet is
part of an addin which the user access via a vba userform. The application
works fine on my desktop and returns correct date values. However when I
transfer application to my laptop date value for year shows 1900?

code I use in UserForm_Initialize is to determine last month of holiday year
is:

Private Sub UserForm_Initialize()
Dim startweek As Date
Dim endmonth As Date
'holiday year is same as FY so we need to
'ensure calendar is set for current financial year (FY)
With ThisWorkbook.Worksheets("FormData")
ThisWorkbook.Worksheets("YearlyCalendar").Range("A1").Value = _
.Range("J2").Value
DBFile = .Range("D21").Value
End With
Application.ScreenUpdating = False
Set DBWB = Workbooks.Open(DBFile, ReadOnly:=True, Password:=Passwrd)

'set date variables
startweek = ThisWorkbook.Worksheets("FormData").Range("K8").Value
endmonth = ThisWorkbook.Worksheets("YearlyCalendar").Range("S36").Value

etc etc

variable endmonth = the end of the holiday year - lets say December 2006.
on my desktop, the expression Year(endmonth) returns 2006 but 1900 on my
laptop?? Both machines run XP / office 2003 with all updates.
Can anyone tell me what I need to do to correct this please?

Many thanks

 

[Dave Peterson]

I think you have to verify what's in this cell:

ThisWorkbook.Worksheets("YearlyCalendar").Range("S36").Value

Is it really a date? Is it text that kind of looks like a date?

 

[johnboy]

Dave.
Date is created with formula =INDEX(monthNames,Y36)&"
"&YEAR(INDEX(startDates,Y36))
Cell is formated "mmmm yyyy" value should be seen as a date - as said in my
post, all works fine on the desktop just not on my laptop? I have tried using
CDate and alike but to no avail - I still get 1900 as the year!

 

[Dave Peterson]

This formula:
=INDEX(monthNames,Y36)&" "&YEAR(INDEX(startDates,Y36))
doesn't return a date.

It returns text that looks like a date to you.

If you reformat that cell to (say) mm/dd/yyyy, you'll see that the display
doesn't change. (Number Formatting won't change the display for text values.)

I'm not sure where you added the cDate(), but this worked ok for me:

Option Explicit
Sub testme()
Dim myStr As String
myStr = "July 2006"
Debug.Print CDate(myStr)
End Sub

It returned:
07/01/2006
(in my USA settings (mdy))

=======
So what do you see in that cell (the value, not the formula)? Maybe it's a
spelling error in the month???

Dave.
Date is created with formula =INDEX(monthNames,Y36)&"
"&YEAR(INDEX(startDates,Y36))
Cell is formated "mmmm yyyy" value should be seen as a date - as said in my
post, all works fine on the desktop just not on my laptop? I have tried using
CDate and alike but to no avail - I still get 1900 as the year!

 

[johnboy]

Dave
thanks for response - I added CDate like this:
endmonth = CDate(ThisWorkbook.Worksheets("YearlyCalendar").Range("S36").Value)
and changed variable to a String - but no joy - Just to check calendar is
showing correct dates - I copied it out from the addin & all ok.
I'm just tad puzzled why procedure works ok on desktop but not on my Laptop!