formula for month that is prior to actual

[braadi]

i generate reports for data collected from the previous month (i.e. in
december, i report for november's data). how do i formulate in my reports
the date to show the previous month and year (while automatically updating
according to excel date and time)?

 

[Peo Sjoblom]

One way

=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),0),"MMM YYYY")

 

[TedMi]

=DATE(IF(MONTH(NOW())=1,YEAR(NOW())-1,YEAR(NOW())),
IF(MONTH(NOW())=1,12,MONTH(NOW())-1), 28)
This might be easier to follow if you store intermediate results in cells:
A1: =IF(MONTH(NOW())=1,12,MONTH(NOW())-1)
A2: =IF(A1=12,YEAR(NOW())-1, YEAR(NOW)))

=Date(A2, A1, 28)

This assumes that you put the DATE function in a cell formatted to show
mm/yyyy. In that case, the day arg to the DATE function is arbitrary and can
be any day number which appears in every month (i.e. 1-28).

 

[braadi]

great. thanks so much.

 

[braadi]

i appreciate the help so much. look forward to getting this working now.