Solved

MONTH MINUS ONE

Posted on 2011-05-06
182 Views
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
Question by:Seamus2626

LVL 81

Expert Comment

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

LVL 81

Accepted Solution

To format as desired, use the custom format "D/M/YYYY".

Kevin
0

LVL 43

Assisted Solution

=text(date(0,month(today()),1)-1,"mmm")
0

LVL 31

Assisted Solution

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

LVL 50

Expert Comment

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

Author Closing Comment

Thanks guys!
0

Featured Post

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.