Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
calendar date problem
I got a popup calendar by using http://www.rondebruin.nl/calendar.htm method.
It functions perfectly under new workbook When I place it in my current project, popup calendar looks normal, but when date clicked 18 Jun 2009 cell recorded as 19 Jun 2003, year alway picked as 2003 in cell. Any idea? Rgds |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
calendar date problem
Sorry, year always showing 2013.
"Seeker" wrote: I got a popup calendar by using http://www.rondebruin.nl/calendar.htm method. It functions perfectly under new workbook When I place it in my current project, popup calendar looks normal, but when date clicked 18 Jun 2009 cell recorded as 19 Jun 2003, year alway picked as 2003 in cell. Any idea? Rgds |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
calendar date problem
--You mean to say even if you select the year from top right corner as 2009;
and click on June 18th; it shows as 2013? In design mode check the last property which is the year; change that to 2009; so that the default year will be 2009 --Incase you want the date in only one cell then refer the cell (say A1) from properties 'Linked Cell' and exit design mode. --If you want the current date to be set as the default, in design mode double click the calendar control and paste the below code. Private Sub Calendar1_GotFocus() Calendar1.Value = Date End Sub If this post helps click Yes --------------- Jacob Skaria "Seeker" wrote: Sorry, year always showing 2013. "Seeker" wrote: I got a popup calendar by using http://www.rondebruin.nl/calendar.htm method. It functions perfectly under new workbook When I place it in my current project, popup calendar looks normal, but when date clicked 18 Jun 2009 cell recorded as 19 Jun 2003, year alway picked as 2003 in cell. Any idea? Rgds |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
calendar date problem
Hi Jacob,
I am using calendar version 10.0. In the popup title and dropdown list also showing 2009 but the cell captured as 2013. I changed the value of default year to 2005, so the dropdown list default as year 2005 then cell captured as 2009, so there always 4 years and 1 day more than the selected date. Rgds "Jacob Skaria" wrote: --You mean to say even if you select the year from top right corner as 2009; and click on June 18th; it shows as 2013? In design mode check the last property which is the year; change that to 2009; so that the default year will be 2009 --Incase you want the date in only one cell then refer the cell (say A1) from properties 'Linked Cell' and exit design mode. --If you want the current date to be set as the default, in design mode double click the calendar control and paste the below code. Private Sub Calendar1_GotFocus() Calendar1.Value = Date End Sub If this post helps click Yes --------------- Jacob Skaria "Seeker" wrote: Sorry, year always showing 2013. "Seeker" wrote: I got a popup calendar by using http://www.rondebruin.nl/calendar.htm method. It functions perfectly under new workbook When I place it in my current project, popup calendar looks normal, but when date clicked 18 Jun 2009 cell recorded as 19 Jun 2003, year alway picked as 2003 in cell. Any idea? Rgds |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
calendar date problem
Your problem is this
By default, Excel in Microsoft Windows uses the 1900 date system. However, many Apple Macintosh users use the 1904 date system. Dates in Excel are actually numbers. That is, if you enter 1 in an empty cell and format the cell as date, 1900-01-01 is displayed in the 1900 date system, and 1904-01-02 is displayed in the 1904 date system. To change this setting in Excel 97-Excel 2003, on the Tools menu, click Options, and then click the Calculation tab. To change the setting in Excel 2007, click the Microsoft Office Button, click the Excel Options dialog box, and then click the Advanced tab. If you copy dates between workbooks, and both workbooks use a different date system, there is a difference of four years (1462 days) between the dates. The following steps illustrate one way to compensate for this difference: Type 1462 into any empty cell in the workbook. Copy the contents by selecting the cell and pressing CTRL+C. Select the cell that contains the date you want to update. Excel 2007 only: On the Home tab, in the Clipboard group, click Paste, click Paste Special, select Add (or Subtract depending on which workbook you are pasting to), and then click OK. -or- Excel 97, Excel 2000, Excel 2002, and Excel 2003 only: On the Edit menu, click Paste Special, click Add (or Subtract depending on which workbook you are pasting to), and then click OK. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Seeker" wrote in message ... Hi Jacob, I am using calendar version 10.0. In the popup title and dropdown list also showing 2009 but the cell captured as 2013. I changed the value of default year to 2005, so the dropdown list default as year 2005 then cell captured as 2009, so there always 4 years and 1 day more than the selected date. Rgds "Jacob Skaria" wrote: --You mean to say even if you select the year from top right corner as 2009; and click on June 18th; it shows as 2013? In design mode check the last property which is the year; change that to 2009; so that the default year will be 2009 --Incase you want the date in only one cell then refer the cell (say A1) from properties 'Linked Cell' and exit design mode. --If you want the current date to be set as the default, in design mode double click the calendar control and paste the below code. Private Sub Calendar1_GotFocus() Calendar1.Value = Date End Sub If this post helps click Yes --------------- Jacob Skaria "Seeker" wrote: Sorry, year always showing 2013. "Seeker" wrote: I got a popup calendar by using http://www.rondebruin.nl/calendar.htm method. It functions perfectly under new workbook When I place it in my current project, popup calendar looks normal, but when date clicked 18 Jun 2009 cell recorded as 19 Jun 2003, year alway picked as 2003 in cell. Any idea? Rgds |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
calendar date problem
Ron,
Thanks very much for your macro and your tips in solving the problem. Millionnnnnnnn tks again. Rgds "Ron de Bruin" wrote: Your problem is this By default, Excel in Microsoft Windows uses the 1900 date system. However, many Apple Macintosh users use the 1904 date system. Dates in Excel are actually numbers. That is, if you enter 1 in an empty cell and format the cell as date, 1900-01-01 is displayed in the 1900 date system, and 1904-01-02 is displayed in the 1904 date system. To change this setting in Excel 97-Excel 2003, on the Tools menu, click Options, and then click the Calculation tab. To change the setting in Excel 2007, click the Microsoft Office Button, click the Excel Options dialog box, and then click the Advanced tab. If you copy dates between workbooks, and both workbooks use a different date system, there is a difference of four years (1462 days) between the dates. The following steps illustrate one way to compensate for this difference: Type 1462 into any empty cell in the workbook. Copy the contents by selecting the cell and pressing CTRL+C. Select the cell that contains the date you want to update. Excel 2007 only: On the Home tab, in the Clipboard group, click Paste, click Paste Special, select Add (or Subtract depending on which workbook you are pasting to), and then click OK. -or- Excel 97, Excel 2000, Excel 2002, and Excel 2003 only: On the Edit menu, click Paste Special, click Add (or Subtract depending on which workbook you are pasting to), and then click OK. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Seeker" wrote in message ... Hi Jacob, I am using calendar version 10.0. In the popup title and dropdown list also showing 2009 but the cell captured as 2013. I changed the value of default year to 2005, so the dropdown list default as year 2005 then cell captured as 2009, so there always 4 years and 1 day more than the selected date. Rgds "Jacob Skaria" wrote: --You mean to say even if you select the year from top right corner as 2009; and click on June 18th; it shows as 2013? In design mode check the last property which is the year; change that to 2009; so that the default year will be 2009 --Incase you want the date in only one cell then refer the cell (say A1) from properties 'Linked Cell' and exit design mode. --If you want the current date to be set as the default, in design mode double click the calendar control and paste the below code. Private Sub Calendar1_GotFocus() Calendar1.Value = Date End Sub If this post helps click Yes --------------- Jacob Skaria "Seeker" wrote: Sorry, year always showing 2013. "Seeker" wrote: I got a popup calendar by using http://www.rondebruin.nl/calendar.htm method. It functions perfectly under new workbook When I place it in my current project, popup calendar looks normal, but when date clicked 18 Jun 2009 cell recorded as 19 Jun 2003, year alway picked as 2003 in cell. Any idea? Rgds |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
calendar date problem
Hi Ron/ Jacob,
The calendar is ready for users to select date for record now, I also made two validation cells for hour and minute selection, the problem is how do I embed these three cells in the fourth cell as value for calculation? Rgds "Ron de Bruin" wrote: Your problem is this By default, Excel in Microsoft Windows uses the 1900 date system. However, many Apple Macintosh users use the 1904 date system. Dates in Excel are actually numbers. That is, if you enter 1 in an empty cell and format the cell as date, 1900-01-01 is displayed in the 1900 date system, and 1904-01-02 is displayed in the 1904 date system. To change this setting in Excel 97-Excel 2003, on the Tools menu, click Options, and then click the Calculation tab. To change the setting in Excel 2007, click the Microsoft Office Button, click the Excel Options dialog box, and then click the Advanced tab. If you copy dates between workbooks, and both workbooks use a different date system, there is a difference of four years (1462 days) between the dates. The following steps illustrate one way to compensate for this difference: Type 1462 into any empty cell in the workbook. Copy the contents by selecting the cell and pressing CTRL+C. Select the cell that contains the date you want to update. Excel 2007 only: On the Home tab, in the Clipboard group, click Paste, click Paste Special, select Add (or Subtract depending on which workbook you are pasting to), and then click OK. -or- Excel 97, Excel 2000, Excel 2002, and Excel 2003 only: On the Edit menu, click Paste Special, click Add (or Subtract depending on which workbook you are pasting to), and then click OK. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Seeker" wrote in message ... Hi Jacob, I am using calendar version 10.0. In the popup title and dropdown list also showing 2009 but the cell captured as 2013. I changed the value of default year to 2005, so the dropdown list default as year 2005 then cell captured as 2009, so there always 4 years and 1 day more than the selected date. Rgds "Jacob Skaria" wrote: --You mean to say even if you select the year from top right corner as 2009; and click on June 18th; it shows as 2013? In design mode check the last property which is the year; change that to 2009; so that the default year will be 2009 --Incase you want the date in only one cell then refer the cell (say A1) from properties 'Linked Cell' and exit design mode. --If you want the current date to be set as the default, in design mode double click the calendar control and paste the below code. Private Sub Calendar1_GotFocus() Calendar1.Value = Date End Sub If this post helps click Yes --------------- Jacob Skaria "Seeker" wrote: Sorry, year always showing 2013. "Seeker" wrote: I got a popup calendar by using http://www.rondebruin.nl/calendar.htm method. It functions perfectly under new workbook When I place it in my current project, popup calendar looks normal, but when date clicked 18 Jun 2009 cell recorded as 19 Jun 2003, year alway picked as 2003 in cell. Any idea? Rgds |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
calendar date problem
You can have a formula in the 4th column
=A1+TIMEVALUE(B1&":"&C1) OR in from code itself you directly calculate this as dtTemp + timevalue( intHour & ":" & intMin ) where dtTemp is the selected date (date variable) intHour is hour declared as integer intMin is minutes declared as integer If this post helps click Yes --------------- Jacob Skaria "Seeker" wrote: Hi Ron/ Jacob, The calendar is ready for users to select date for record now, I also made two validation cells for hour and minute selection, the problem is how do I embed these three cells in the fourth cell as value for calculation? Rgds "Ron de Bruin" wrote: Your problem is this By default, Excel in Microsoft Windows uses the 1900 date system. However, many Apple Macintosh users use the 1904 date system. Dates in Excel are actually numbers. That is, if you enter 1 in an empty cell and format the cell as date, 1900-01-01 is displayed in the 1900 date system, and 1904-01-02 is displayed in the 1904 date system. To change this setting in Excel 97-Excel 2003, on the Tools menu, click Options, and then click the Calculation tab. To change the setting in Excel 2007, click the Microsoft Office Button, click the Excel Options dialog box, and then click the Advanced tab. If you copy dates between workbooks, and both workbooks use a different date system, there is a difference of four years (1462 days) between the dates. The following steps illustrate one way to compensate for this difference: Type 1462 into any empty cell in the workbook. Copy the contents by selecting the cell and pressing CTRL+C. Select the cell that contains the date you want to update. Excel 2007 only: On the Home tab, in the Clipboard group, click Paste, click Paste Special, select Add (or Subtract depending on which workbook you are pasting to), and then click OK. -or- Excel 97, Excel 2000, Excel 2002, and Excel 2003 only: On the Edit menu, click Paste Special, click Add (or Subtract depending on which workbook you are pasting to), and then click OK. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Seeker" wrote in message ... Hi Jacob, I am using calendar version 10.0. In the popup title and dropdown list also showing 2009 but the cell captured as 2013. I changed the value of default year to 2005, so the dropdown list default as year 2005 then cell captured as 2009, so there always 4 years and 1 day more than the selected date. Rgds "Jacob Skaria" wrote: --You mean to say even if you select the year from top right corner as 2009; and click on June 18th; it shows as 2013? In design mode check the last property which is the year; change that to 2009; so that the default year will be 2009 --Incase you want the date in only one cell then refer the cell (say A1) from properties 'Linked Cell' and exit design mode. --If you want the current date to be set as the default, in design mode double click the calendar control and paste the below code. Private Sub Calendar1_GotFocus() Calendar1.Value = Date End Sub If this post helps click Yes --------------- Jacob Skaria "Seeker" wrote: Sorry, year always showing 2013. "Seeker" wrote: I got a popup calendar by using http://www.rondebruin.nl/calendar.htm method. It functions perfectly under new workbook When I place it in my current project, popup calendar looks normal, but when date clicked 18 Jun 2009 cell recorded as 19 Jun 2003, year alway picked as 2003 in cell. Any idea? Rgds |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
calendar date problem
Tks Jacob
"Jacob Skaria" wrote: You can have a formula in the 4th column =A1+TIMEVALUE(B1&":"&C1) OR in from code itself you directly calculate this as dtTemp + timevalue( intHour & ":" & intMin ) where dtTemp is the selected date (date variable) intHour is hour declared as integer intMin is minutes declared as integer If this post helps click Yes --------------- Jacob Skaria "Seeker" wrote: Hi Ron/ Jacob, The calendar is ready for users to select date for record now, I also made two validation cells for hour and minute selection, the problem is how do I embed these three cells in the fourth cell as value for calculation? Rgds "Ron de Bruin" wrote: Your problem is this By default, Excel in Microsoft Windows uses the 1900 date system. However, many Apple Macintosh users use the 1904 date system. Dates in Excel are actually numbers. That is, if you enter 1 in an empty cell and format the cell as date, 1900-01-01 is displayed in the 1900 date system, and 1904-01-02 is displayed in the 1904 date system. To change this setting in Excel 97-Excel 2003, on the Tools menu, click Options, and then click the Calculation tab. To change the setting in Excel 2007, click the Microsoft Office Button, click the Excel Options dialog box, and then click the Advanced tab. If you copy dates between workbooks, and both workbooks use a different date system, there is a difference of four years (1462 days) between the dates. The following steps illustrate one way to compensate for this difference: Type 1462 into any empty cell in the workbook. Copy the contents by selecting the cell and pressing CTRL+C. Select the cell that contains the date you want to update. Excel 2007 only: On the Home tab, in the Clipboard group, click Paste, click Paste Special, select Add (or Subtract depending on which workbook you are pasting to), and then click OK. -or- Excel 97, Excel 2000, Excel 2002, and Excel 2003 only: On the Edit menu, click Paste Special, click Add (or Subtract depending on which workbook you are pasting to), and then click OK. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Seeker" wrote in message ... Hi Jacob, I am using calendar version 10.0. In the popup title and dropdown list also showing 2009 but the cell captured as 2013. I changed the value of default year to 2005, so the dropdown list default as year 2005 then cell captured as 2009, so there always 4 years and 1 day more than the selected date. Rgds "Jacob Skaria" wrote: --You mean to say even if you select the year from top right corner as 2009; and click on June 18th; it shows as 2013? In design mode check the last property which is the year; change that to 2009; so that the default year will be 2009 --Incase you want the date in only one cell then refer the cell (say A1) from properties 'Linked Cell' and exit design mode. --If you want the current date to be set as the default, in design mode double click the calendar control and paste the below code. Private Sub Calendar1_GotFocus() Calendar1.Value = Date End Sub If this post helps click Yes --------------- Jacob Skaria "Seeker" wrote: Sorry, year always showing 2013. "Seeker" wrote: I got a popup calendar by using http://www.rondebruin.nl/calendar.htm method. It functions perfectly under new workbook When I place it in my current project, popup calendar looks normal, but when date clicked 18 Jun 2009 cell recorded as 19 Jun 2003, year alway picked as 2003 in cell. Any idea? Rgds |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert date from Gregorian Calendar to Hijri Calendar | Excel Discussion (Misc queries) | |||
How to link an Excel file due date to Outlook calendar date? | New Users to Excel | |||
Problem with calendar planner date ranges | Excel Programming | |||
company calendar: start date problem | Excel Programming | |||
have a date cell pop up a monthly calendar to choose a date | Excel Worksheet Functions |