• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

MONTH MINUS ONE

Hi,

Is there formula to calculate what was the last month based on todays date. So today it would be APRIL,

Can it also be formatted so it would return 01/04/2011

Thanks
Seamus
0
Seamus2626
Asked:
Seamus2626
3 Solutions
 
zorvek (Kevin Jones)ConsultantCommented:
The first of the previous month:

   =DATE(YEAR(A1), MONTH(A1), 0)-DAY(DATE(YEAR(A1), MONTH(A1), 0))+1

Format as desired.

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
To format as desired, use the custom format "D/M/YYYY".

Kevin
0
 
Saqib Husain, SyedEngineerCommented:
=text(date(0,month(today()),1)-1,"mmm")
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rob HensonIT & Database AssistantCommented:
You can use the EOMONTH function to do that.

Syntax = EOMONTH(Date,Offset)

Date - reference to cell with date or date related function eg TODAY().

Offset - Can be positive or negative. 0 would return last day of current month, -1 would return last day of previous month.

Therefore = EOMONTH(TODAY(),-2)+1

Would return 1st day of previous month. Then use standard formatting to show as required.

EOMONTH does need the Analysis ToolPak AddIn enabled.

Cheers
Rob H
0
 
barry houdiniCommented:
If you have today's date in A1 you can get that date like this

=EOMONTH(A1,-2)+1

or

=A1-DAY(A1)-DAY(A1-DAY(A1))+1

or

=DATE(YEAR(A1),MONTH(A1)-1,1)

format as required

regards, barry
0
 
Seamus2626Author Commented:
Thanks guys!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now