# formula for month that is prior to actual

1. ## formula for month that is prior to actual

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)?

2. ## Re: formula for month that is prior to actual

One way

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

--

Regards,

Peo Sjoblom

> 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)?

3. ## RE: formula for month that is prior to actual

=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).
--
Ted

4. ## RE: formula for month that is prior to actual

great. thanks so much.

"TedMi" wrote:

> =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).
> --
> Ted
>

5. ## Re: formula for month that is prior to actual

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

"Peo Sjoblom" wrote:

> One way
>
> =TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),0),"MMM YYYY")
>
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
>
> > 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)?

>
>
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1