get previous month in cell

[somenoob]

Hi everyone, i have a question to ask​

i need to set to be previous month of the current date in cell D16 on startup of the excel.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
2. For year in cell D15, if current month is Jan, then use previous year, else will use current year.​

How do i code that out?
i have the following codes out now..​

Sub ThisDate()
'display current month. eg Sep. For September, change to mmmm.
mth = UCase(Format(Date, "mmm"))
'Edit the position for the date to be displayed in the .Range("_") field.
Sheets("List").Range("D16").Value = mth
' display current year. eg FY2011.
yr = UCase(Format(Date, "yyyy"))
'Edit the position for the date to be displayed in the .Range("_") field.
Sheets("List").Range("D15").Value = "FY" + yr
End Sub​

Thanks​

 

[Sal Paradise]

You can do it simply with a formula:
=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))

You could do the same in VBA using Application.WorksheetFunction most likely.

 

[somenoob]

is there a way to code it in vba. as the date might get changed by manually typing it to other dates. as now the previous month is aug. but i might manually type it to feb for eg.

 

[somenoob]

any help? please

 

[VoG]

Date minus one month in VBA

aDate = DateAdd("m", -1, Date)

 

[somenoob]

Thanks. It works!