Show Previous Months From Date Using Worksheet Formula?

**dj_siek** · 03-01-2007, 07:07 PM

hi all,

quick and easy one - if i wanted to show only previous month results. how would i do it.

if i had a cell that had a date in it (not today() but actual date 1/2/07) how would i have it so its cell minus one month?

Thanks

Joel

**oldchippy** · 03-01-2007, 07:18 PM

If you have 01/02/2007 in A1, then

=DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)) will be 1 month before

**dj_siek** · 03-01-2007, 07:34 PM

thanks old chippy.

joel

**dj_siek** · 03-01-2007, 07:36 PM

thanks old chippy.

the only prob with that is what is its the first month of the year would it still work?

Cheers

joel

**oldchippy** · 03-02-2007, 04:53 AM

Originally Posted by dj_siek

thanks old chippy.

the only prob with that is what is its the first month of the year would it still work?

Cheers

joel

Yes, it would just go to the previous month i.e. December the previous year. If you didn't want to show a date if it was the previous year, then

=IF(MONTH(A1)=1,"",DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))) which say's if the month in A1 is 1 (January), display blank, otherwise show date one month previous.

**daddylonglegs** · 03-02-2007, 09:55 AM

Originally Posted by dj_siek

hi all,

quick and easy one - if i wanted to show only previous month results. how would i do it.

if i had a cell that had a date in it (not today() but actual date 1/2/07) how would i have it so its cell minus one month?

Thanks

Joel

What might the date be, could it be any day in the month? If you have 31st March 2007 in A1 what result would you want?

If you use the formula

=DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))

this will give 3rd March 2007. If you want to get 28th Feb 2007 then perhaps try using EDATE from Analysis ToolPak,

=EDATE(A1,-1)