# question about calculating last day of previous month

#### ces005

##### Board Regular
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

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

=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.

Thanks a lot for your help.

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

ces005

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

formula worked

That worked really well. Thanks a lot.

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

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.

question about applying formula within script

Hi Peter,

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

-Eileen

Eileen

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

Do you want to create a user defined function?

Replies
3
Views
109
Replies
3
Views
318
Replies
3
Views
243
Replies
2
Views
141
Replies
0
Views
165

1,218,575
Messages
6,143,311
Members
450,477
Latest member
teresab543

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back