| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dates
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("A 1").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").Va lue endmonth = ThisWorkbook.Worksheets("YearlyCalendar").Range("S 36").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 -- JB |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dates
I think you have to verify what's in this cell:
ThisWorkbook.Worksheets("YearlyCalendar").Range("S 36").Value Is it really a date? Is it text that kind of looks like a date? johnboy wrote: 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("A 1").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").Va lue endmonth = ThisWorkbook.Worksheets("YearlyCalendar").Range("S 36").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 -- JB -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dates
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! -- JB "Dave Peterson" wrote: I think you have to verify what's in this cell: ThisWorkbook.Worksheets("YearlyCalendar").Range("S 36").Value Is it really a date? Is it text that kind of looks like a date? johnboy wrote: 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("A 1").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").Va lue endmonth = ThisWorkbook.Worksheets("YearlyCalendar").Range("S 36").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 -- JB -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dates
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??? johnboy wrote: 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! -- JB "Dave Peterson" wrote: I think you have to verify what's in this cell: ThisWorkbook.Worksheets("YearlyCalendar").Range("S 36").Value Is it really a date? Is it text that kind of looks like a date? johnboy wrote: 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("A 1").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").Va lue endmonth = ThisWorkbook.Worksheets("YearlyCalendar").Range("S 36").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 -- JB -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dates
Dave
thanks for response - I added CDate like this: endmonth = CDate(ThisWorkbook.Worksheets("YearlyCalendar").Ra nge("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! -- JB "Dave Peterson" wrote: 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??? johnboy wrote: 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! -- JB "Dave Peterson" wrote: I think you have to verify what's in this cell: ThisWorkbook.Worksheets("YearlyCalendar").Range("S 36").Value Is it really a date? Is it text that kind of looks like a date? johnboy wrote: 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("A 1").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").Va lue endmonth = ThisWorkbook.Worksheets("YearlyCalendar").Range("S 36").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 -- JB -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Linking computer dates (time) to spreadsheet dates that have formu | Excel Worksheet Functions | |||
| Toggle a range of Julian dates to Gregorian Dates and Back | Excel Programming | |||
| Identifying unique dates in a range of cells containing dates... | Excel Discussion (Misc queries) | |||
| need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
| Charting data against dates where dates are not at fixed intervals | Charts and Charting in Excel | |||
Linear Mode
