question about calculating last day of previous month

[ces005]

Hi,

If the current date is the last day of the current month, then I want to use that date, otherwise I want to use the last day of the previous month. How do you write this kind of logic? Thanks.

-Eileen

 

[GorD]

How about

=IF(A1=EOMONTH(A1,0),A1,EOMONTH(A1,-1)) where your date is in A1 [=today()]

From memory EOmonth probably needs tha analysis tool pack installed.

 

[ces005]

Thanks a lot for your help.

I have the analysis tool pack installed already because of other date functions I am using.

 

[Peter_SSs]

ces005

Try this one:
=EOMONTH(A1+1,-1)

 

[ces005]

formula worked

That worked really well. Thanks a lot.

 

[ces005]

If I am distributing this to a lot of people and don't want to ask everyone to install the analysis pack. Is there some other way to do this logic within a visual basic script? I think the pseudo-code looks something like this. EndDate is the last day of the previous month if the current date is not the last day of the month.

EndDate = Date
' determine the month and year of next month
if (current month is December)
set TempYear to (year + 1)
set TempMonth to Jan
else
set TempYear to current year
set TempMonth to (current month + 1)
end if

if (TempMonth/1/TempYear) - 1 = Date
' current date is last day in the month
' use that date
EndDate = Date
else
EndDate = (current month/1/current year - 1
end if



-Eileen

 

[Peter_SSs]

ces005

If you do not want to rely on Analysis Toolpak, you still don't need VBA. Try this formula:
=DATE(YEAR(A1+1),MONTH(A1+1),1)-1
where A1 houses =TODAY()

This could be combined into a single formula:
=DATE(YEAR(TODAY()+1),MONTH(TODAY()+1),1)-1
though since TODAY() is a 'volatile' function, it is generally best to use it as few times as possible on a sheet.

 

[ces005]

question about applying formula within script

Hi Peter,

How would I apply the formula you gave within a visual basic script? Thanks.

-Eileen

 

[Norie]

Eileen

Can you explain further how you want to use this in VBA?

Do you want to create a user defined function?