Hello all,

I'm working on a project that is a little over my head and appreciate
any assistance available. I'm editing code for a PeopleSoft form and
want to capture the last day of the month. The code is currently
capturing the current date. I think I've isolated the lines that set
the date. Can anyone assist with code for the last day of the month?
Thank you for your assistance, Ron

If Workbooks(CurWrkBook).DialogSheets(OptionSheet).Ed itBoxes
("DefaultDate").Text = "" Then
HdrDlg.EditBoxes(3).Text = Format(Date, "General Date")
HdrDlg.EditBoxes("Edit Box EffDate").Text = Format(Date,
"General Date")
Else
HdrDlg.EditBoxes(3).Text = Workbooks(CurWrkBook).DialogSheets
(OptionSheet).EditBoxes("DefaultDate").Text
HdrDlg.EditBoxes("Edit Box EffDate").Text = Workbooks
(CurWrkBook).DialogSheets(OptionSheet).EditBoxes(" DefaultDate").Text
End If

Ron-

Where you currently have Date, try:
cdate((month(date())+1)& "/" & year(date()))-1

This takes the current month and adds one (so it evaluates to the first day
of next month), then subtracting one at the end subtracts one day, leaving
you with the last date of the current month, regardless of whether it has 28,
29 (leap year), 30, or 31 days.

HTH,
Keith

"Ron" wrote:

Hello all,

I'm working on a project that is a little over my head and appreciate
any assistance available. I'm editing code for a PeopleSoft form and
want to capture the last day of the month. The code is currently
capturing the current date. I think I've isolated the lines that set
the date. Can anyone assist with code for the last day of the month?
Thank you for your assistance, Ron

If Workbooks(CurWrkBook).DialogSheets(OptionSheet).Ed itBoxes
("DefaultDate").Text = "" Then
HdrDlg.EditBoxes(3).Text = Format(Date, "General Date")
HdrDlg.EditBoxes("Edit Box EffDate").Text = Format(Date,
"General Date")
Else
HdrDlg.EditBoxes(3).Text = Workbooks(CurWrkBook).DialogSheets
(OptionSheet).EditBoxes("DefaultDate").Text
HdrDlg.EditBoxes("Edit Box EffDate").Text = Workbooks
(CurWrkBook).DialogSheets(OptionSheet).EditBoxes(" DefaultDate").Text
End If

Hi Keith, worked fantastic. One quick one. These dates flow to cells
on the spreadsheet. I have a validation process that loops through
the sheet validating different formats. How would I include the date
cells? I just want to validate that the date year is the current year
or the next year i.e. 2009 or 2010. Thanks again your code is greatly
appreciated, Ron

On Aug 12, 2:50*pm, ker_01 wrote:
Ron-

Where you currently have Date, try:
cdate((month(date())+1)& "/" *& year(date()))-1

This takes the current month and adds one (so it evaluates to the first day
of next month), then subtracting one at the end subtracts one day, leaving
you with the last date of the current month, regardless of whether it has 28,
29 (leap year), 30, or 31 days.

HTH,
Keith



"Ron" wrote:
Hello all,

I'm working on a project that is a little over my head and appreciate
any assistance available. *I'm editing code for a PeopleSoft form and
want to capture the last day of the month. *The code is currently
capturing the current date. *I think I've isolated the lines that set
the date. *Can anyone assist with code for the last day of the month?
Thank you for your assistance, Ron

If Workbooks(CurWrkBook).DialogSheets(OptionSheet).Ed itBoxes
("DefaultDate").Text = "" Then
* * * * HdrDlg.EditBoxes(3).Text = Format(Date, "General Date")
* * * * HdrDlg.EditBoxes("Edit Box EffDate").Text = Format(Date,
"General Date")
* * Else
* * * * HdrDlg.EditBoxes(3).Text = Workbooks(CurWrkBook).DialogSheets
(OptionSheet).EditBoxes("DefaultDate").Text
* * * * HdrDlg.EditBoxes("Edit Box EffDate").Text = Workbooks
(CurWrkBook).DialogSheets(OptionSheet).EditBoxes(" DefaultDate").Text
* * End If- Hide quoted text -

- Show quoted text -

Ron-

To validate withini the sheet based on year (in this example, in cell A1),
for example if you are using conditional formatting to highlight cells that
contain values outside of your range, use something like:

=if(or((year(A1)=2009),(year(A1)=2010)),true,false )
and put in whatever conditions you want for true or false.

or in VBA,

if (year(A1)=2009) or (year(A1)=2010) then
'do stuff, or maybe do nothing at all
else
'do something else, like highlighting the cells or create a msgbox to
indicate where a cell with an invalid date has been found
endif

In retrospect, my previous answer was incomplete. It adds a month then
removes a day, and that works for every month except for December. It can't
evaluate month "13", so it needs extra logic to also work for Dec dates.

This formula takes this into account, and should work for any date:
cdate (((month(date())+1)-((month(date())\12)*12))& "/" &
(year(date())+(month(date())\12)))-1

HTH,
Keith

"Ron" wrote:

Hi Keith, worked fantastic. One quick one. These dates flow to cells
on the spreadsheet. I have a validation process that loops through
the sheet validating different formats. How would I include the date
cells? I just want to validate that the date year is the current year
or the next year i.e. 2009 or 2010. Thanks again your code is greatly
appreciated, Ron

On Aug 12, 2:50 pm, ker_01 wrote:
Ron-

Where you currently have Date, try:
cdate((month(date())+1)& "/" & year(date()))-1

This takes the current month and adds one (so it evaluates to the first day
of next month), then subtracting one at the end subtracts one day, leaving
you with the last date of the current month, regardless of whether it has 28,
29 (leap year), 30, or 31 days.

HTH,
Keith



"Ron" wrote:
Hello all,

I'm working on a project that is a little over my head and appreciate
any assistance available. I'm editing code for a PeopleSoft form and
want to capture the last day of the month. The code is currently
capturing the current date. I think I've isolated the lines that set
the date. Can anyone assist with code for the last day of the month?
Thank you for your assistance, Ron

If Workbooks(CurWrkBook).DialogSheets(OptionSheet).Ed itBoxes
("DefaultDate").Text = "" Then
HdrDlg.EditBoxes(3).Text = Format(Date, "General Date")
HdrDlg.EditBoxes("Edit Box EffDate").Text = Format(Date,
"General Date")
Else
HdrDlg.EditBoxes(3).Text = Workbooks(CurWrkBook).DialogSheets
(OptionSheet).EditBoxes("DefaultDate").Text
HdrDlg.EditBoxes("Edit Box EffDate").Text = Workbooks
(CurWrkBook).DialogSheets(OptionSheet).EditBoxes(" DefaultDate").Text
End If- Hide quoted text -

- Show quoted text -

Hi Keith, thank you for your excellent resolution. Ron


On Aug 13, 8:34*am, ker_01 wrote:
Ron-

To validate withini the sheet based on year (in this example, in cell A1),
for example if you are using conditional formatting to highlight cells that
contain values outside of your range, use something like:

=if(or((year(A1)=2009),(year(A1)=2010)),true,false )
and put in whatever conditions you want for true or false.

or in VBA,

if (year(A1)=2009) or (year(A1)=2010) then
*'do stuff, or maybe do nothing at all
else
*'do something else, like highlighting the cells or create a msgbox to
indicate where a cell with an invalid date has been found
endif

In retrospect, my previous answer was incomplete. It adds a month then
removes a day, and that works for every month except for December. It can't
evaluate month "13", so it needs extra logic to also work for Dec dates.

This formula takes this into account, and should work for any date:
cdate (((month(date())+1)-((month(date())\12)*12))& "/" *&
(year(date())+(month(date())\12)))-1

HTH,
Keith



"Ron" wrote:
Hi Keith, worked fantastic. *One quick one. *These dates flow to cells
on the spreadsheet. *I have a validation process that loops through
the sheet validating different formats. *How would I include the date
cells? *I just want to validate that the date year is the current year
or the next year i.e. 2009 or 2010. Thanks again your code is greatly
appreciated, Ron

On Aug 12, 2:50 pm, ker_01 wrote:
Ron-

Where you currently have Date, try:
cdate((month(date())+1)& "/" *& year(date()))-1

This takes the current month and adds one (so it evaluates to the first day
of next month), then subtracting one at the end subtracts one day, leaving
you with the last date of the current month, regardless of whether it has 28,
29 (leap year), 30, or 31 days.

HTH,
Keith

"Ron" wrote:
Hello all,

I'm working on a project that is a little over my head and appreciate
any assistance available. *I'm editing code for a PeopleSoft form and
want to capture the last day of the month. *The code is currently
capturing the current date. *I think I've isolated the lines that set
the date. *Can anyone assist with code for the last day of the month?
Thank you for your assistance, Ron

If Workbooks(CurWrkBook).DialogSheets(OptionSheet).Ed itBoxes
("DefaultDate").Text = "" Then
* * * * HdrDlg.EditBoxes(3).Text = Format(Date, "General Date")
* * * * HdrDlg.EditBoxes("Edit Box EffDate").Text = Format(Date,
"General Date")
* * Else
* * * * HdrDlg.EditBoxes(3).Text = Workbooks(CurWrkBook).DialogSheets
(OptionSheet).EditBoxes("DefaultDate").Text
* * * * HdrDlg.EditBoxes("Edit Box EffDate").Text = Workbooks
(CurWrkBook).DialogSheets(OptionSheet).EditBoxes(" DefaultDate").Text
* * End If- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -