Subtract 1 year from date based on month "January"

kripper

Board Regular
Joined
Dec 16, 2013
Messages
102
I am trying to figure out how I can subtract a year when I formulate based on if the current month is January.

Such as Current month = January 2014, I would require the cell to populate December 2013, any other month would return the current year value.

I have currently tried { =DATE(YEAR(A3)-1,MONTH(A3),DAY(A3)) } whereas cell A3 uses NOW() function formatted to MMMM, but it changes for every month.

Hoping for some help.

Thanks
K.
 
Sorry again.....maybe I should have asked this when I haven't been up for 18 hours, then trying to decipher this formula. Yes, I would require any days in March such as the 29th, 30th, and 31st to go to the last day in February.

Thanks for the help, and sorry again for not quite catching the original question.
 
Upvote 0

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sorry again.....maybe I should have asked this when I haven't been up for 18 hours, then trying to decipher this formula. Yes, I would require any days in March such as the 29th, 30th, and 31st to go to the last day in February.

Thanks for the help, and sorry again for not quite catching the original question.

Okay, try this formula...

=EDATE(A3,-1)
 
Upvote 0
Sorry again.....maybe I should have asked this when I haven't been up for 18 hours, then trying to decipher this formula. Yes, I would require any days in March such as the 29th, 30th, and 31st to go to the last day in February.

Thanks for the help, and sorry again for not quite catching the original question.

How do you enter February 2014 in a cell in order to get January 2014 in another cell?
 
Upvote 0
Okay, try this formula...

=EDATE(A3,-1)


Thanks Rick, it is what I was looking for.

I tested by changing system times back several months into 2013 and forward several into 2015, and all appears to function as it should. I was trying so many other formulas, and you simplified it.

Absolutely perfect, thanks again!
 
Upvote 0
Thanks Rick, it is what I was looking for.

I tested by changing system times back several months into 2013 and forward several into 2015, and all appears to function as it should. I was trying so many other formulas, and you simplified it.

Absolutely perfect, thanks again!

=EOMONTH(A3,-2)+1

should do it too. The result you would get woulkd be a first day date, not any day date.
 
Upvote 0
Thank you so much for the assistance. It have tried and SUCCESS! I can't thank you enough for the help!
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top