month

X

xxrch75j

hi everyone,
what function i can use so when i enter the date it will give me back the
last month
e.g. i enter "01/07/2004" it will give me back "Jun-04"
 
F

Frank Kabel

Hi
in in adjacent cell put the formula
=DATE(YEAR(A1),MONTH(A1),0)
and format this cell with the custom format
MMM-YY

or use the formula
=TEXT(DATE(YEAR(A1),MONTH(A1),0),"MMM-YY")
 
J

JulieD

Hi

one option
=EOMONTH(A1,-1)
where the date is entered in A1
you will also need to right mouse click on the cell containing the function
and choose format cells and then choose the format that you want to see.
however you need to have the analysis toolpak installed to use this function

if you don't need the resulting "date" to be a date but would be happy for
it to be text - you can use the following formula which doesn't require the
analysis toolpak
=CHOOSE(MONTH(A1)-1,"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
& "-" & YEAR(A1)

Hope this helps
Cheers
JulieD
 
J

JulieD

Hi Frank

=DATE(YEAR(A1),MONTH(A1),0)

sneaky :) ... i like it :)

Cheers
JulieD
PS xxrch75j, this is a much better solution than mine!
 
F

Frank Kabel

Hi Julie
thanks :)

JulieD said:
Hi Frank

=DATE(YEAR(A1),MONTH(A1),0)

sneaky :) ... i like it :)

Cheers
JulieD
PS xxrch75j, this is a much better solution than mine!
Click to expand...
 
R

Ron Rosenfeld

hi everyone,
what function i can use so when i enter the date it will give me back the
last month
e.g. i enter "01/07/2004" it will give me back "Jun-04"
Click to expand...

With the date in A1:

=A1-DAY(A1) and format as mmm-yy

or

=TEXT(A1-DAY(A1),"mmm-yy")


--ron
 
Top