| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Month
I have a column of dates, and I want a formula that will subtract one month
from that date. I can't simply subtract 30 from the date b/c sometimes that will still be in the same month (i.e. - July 31 - 30 days= July 1)...and I can't simply subtract 31, because seometimes that will be 2 months earlier (i.e.- July 1 - 31=May 31). Any help? Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Month
The previous month is:
=date(year(a1),month(a1)-1,day(a1)) Regards, Fred "lightbulb" wrote in message ... I have a column of dates, and I want a formula that will subtract one month from that date. I can't simply subtract 30 from the date b/c sometimes that will still be in the same month (i.e. - July 31 - 30 days= July 1)...and I can't simply subtract 31, because seometimes that will be 2 months earlier (i.e.- July 1 - 31=May 31). Any help? Thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Month
Try this:
=DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)) Mind you, this will still cause some problems if you start with, say, 30th March, as 30th February does not exist. Similarly 31st July will try to give 31st June, which will then become 1st July. What answers do you want to see in these cases? Hope this helps. Pete On Dec 22, 2:00*pm, lightbulb wrote: I have a column of dates, and I want a formula that will subtract one month from that date. *I can't simply subtract 30 from the date b/c sometimes that will still be in the same month (i.e. - July 31 - 30 days= July 1)...and I can't simply subtract 31, because seometimes that will be 2 months earlier (i.e.- *July 1 - 31=May 31). *Any *help? Thanks! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Month
try this: =EDATE(A1,-1)
[You must have Analysis Toolpak add-in installed in older versions than "2007"] Micky "lightbulb" wrote: I have a column of dates, and I want a formula that will subtract one month from that date. I can't simply subtract 30 from the date b/c sometimes that will still be in the same month (i.e. - July 31 - 30 days= July 1)...and I can't simply subtract 31, because seometimes that will be 2 months earlier (i.e.- July 1 - 31=May 31). Any help? Thanks! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Month
I am working in Excel 2003
"מיכאל (מיקי) אבידן" wrote: try this: =EDATE(A1,-1) [You must have Analysis Toolpak add-in installed in older versions than "2007"] Micky "lightbulb" wrote: I have a column of dates, and I want a formula that will subtract one month from that date. I can't simply subtract 30 from the date b/c sometimes that will still be in the same month (i.e. - July 31 - 30 days= July 1)...and I can't simply subtract 31, because seometimes that will be 2 months earlier (i.e.- July 1 - 31=May 31). Any help? Thanks! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Month
So...!?
'Tools' 'Add-Ins check the 'Analysis Toolpak" 'OK' Micky "lightbulb" wrote: I am working in Excel 2003 "מיכאל (מיקי) אבידן" wrote: try this: =EDATE(A1,-1) [You must have Analysis Toolpak add-in installed in older versions than "2007"] Micky "lightbulb" wrote: I have a column of dates, and I want a formula that will subtract one month from that date. I can't simply subtract 30 from the date b/c sometimes that will still be in the same month (i.e. - July 31 - 30 days= July 1)...and I can't simply subtract 31, because seometimes that will be 2 months earlier (i.e.- July 1 - 31=May 31). Any help? Thanks! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Month
Thanks!
In these cases I want Feb. 28, and June 30....it has to be in the month before... "Pete_UK" wrote: Try this: =DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)) Mind you, this will still cause some problems if you start with, say, 30th March, as 30th February does not exist. Similarly 31st July will try to give 31st June, which will then become 1st July. What answers do you want to see in these cases? Hope this helps. Pete On Dec 22, 2:00 pm, lightbulb wrote: I have a column of dates, and I want a formula that will subtract one month from that date. I can't simply subtract 30 from the date b/c sometimes that will still be in the same month (i.e. - July 31 - 30 days= July 1)...and I can't simply subtract 31, because seometimes that will be 2 months earlier (i.e.- July 1 - 31=May 31). Any help? Thanks! . |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Month
=IF(DAY(A1)DAY(DATE(YEAR(A1),MONTH(A1),0)),DATE(Y EAR(A1),MONTH(A1),0),DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))) HTH. Best wishes Harald "lightbulb" wrote in message ... Thanks! In these cases I want Feb. 28, and June 30....it has to be in the month before... "Pete_UK" wrote: Try this: =DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)) Mind you, this will still cause some problems if you start with, say, 30th March, as 30th February does not exist. Similarly 31st July will try to give 31st June, which will then become 1st July. What answers do you want to see in these cases? Hope this helps. Pete On Dec 22, 2:00 pm, lightbulb wrote: I have a column of dates, and I want a formula that will subtract one month from that date. I can't simply subtract 30 from the date b/c sometimes that will still be in the same month (i.e. - July 31 - 30 days= July 1)...and I can't simply subtract 31, because seometimes that will be 2 months earlier (i.e.- July 1 - 31=May 31). Any help? Thanks! . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| excel to make the days cary over month to month automaticly | New Users to Excel | |||
| Excel 2003 month to month data change grid | Excel Discussion (Misc queries) | |||
| Retrieve data for previous 3, 6, 12 month given current month | Excel Worksheet Functions | |||
| When using MONTH function on Blank Cell!! Returns Month=Jan! | Excel Discussion (Misc queries) | |||
| transfer cell $ amount to other sheet month-to-month without overc | Excel Discussion (Misc queries) | |||
Linear Mode
