Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Formula
I need a formula that will subtract March from June returning only the number
of months. For example: 6/1/08-3/1/08=3 6/1/08-3/15/08=3 6/1/08-4/15/08=2 6/1/08-5/15/08=1 However, 6/1/08-6/1/08 also need to = 1 What I have right now is 6/1/08 in cell B2 and the date to subtract in cell B7 with this formula: SUM(MONTH(B2)-MONTH(B7)) I get the result that I need with all of the above except for when using 6/1/08 in cell B7. How can I modify the formula so that any time that date is used it will also return a 1? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Formula
=if(month(b2)=month(b7),1,month(b2)-month(b7))
-- Hope this helps. If this post was helpfull, please remember to click on the ''''YES'''' button at the bottom of the screen. Thanks, Gary Brown "charlie" wrote: I need a formula that will subtract March from June returning only the number of months. For example: 6/1/08-3/1/08=3 6/1/08-3/15/08=3 6/1/08-4/15/08=2 6/1/08-5/15/08=1 However, 6/1/08-6/1/08 also need to = 1 What I have right now is 6/1/08 in cell B2 and the date to subtract in cell B7 with this formula: SUM(MONTH(B2)-MONTH(B7)) I get the result that I need with all of the above except for when using 6/1/08 in cell B7. How can I modify the formula so that any time that date is used it will also return a 1? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Formula
hi
i didn't use you cell address to test this but it works. make the adjustments to fit your data. =IF(MONTH(C2)-MONTH(B2)=0,1,MONTH(C2)-MONTH(B2)) regards FSt1 "charlie" wrote: I need a formula that will subtract March from June returning only the number of months. For example: 6/1/08-3/1/08=3 6/1/08-3/15/08=3 6/1/08-4/15/08=2 6/1/08-5/15/08=1 However, 6/1/08-6/1/08 also need to = 1 What I have right now is 6/1/08 in cell B2 and the date to subtract in cell B7 with this formula: SUM(MONTH(B2)-MONTH(B7)) I get the result that I need with all of the above except for when using 6/1/08 in cell B7. How can I modify the formula so that any time that date is used it will also return a 1? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Formula
Strictly speaking, you should check for month and year being the same.
Hope this helps. Pete On Jul 10, 6:47*pm, FSt1 wrote: hi i didn't use you cell address to test this but it works. make the adjustments to fit your data. =IF(MONTH(C2)-MONTH(B2)=0,1,MONTH(C2)-MONTH(B2)) regards FSt1 "charlie" wrote: I need a formula that will subtract March from June returning only the number of months. For example: 6/1/08-3/1/08=3 6/1/08-3/15/08=3 6/1/08-4/15/08=2 6/1/08-5/15/08=1 However, 6/1/08-6/1/08 also need to = 1 What I have right now is 6/1/08 in cell B2 and the date to subtract in cell B7 with this formula: SUM(MONTH(B2)-MONTH(B7)) I get the result that I need with all of the above except for when using 6/1/08 in cell B7. *How can I modify the formula so that any time that date is used it will also return a 1?- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Formula
Good point, Pete.
=if(and(month(b2)=month(b7),year(b2)=year(b7)),1,i f(year(b2)=year(b7),month(b2)-month(b7),month(b2)+12-month(b7)) Not tested. -- Hope this helps. If this post was helpfull, please remember to click on the ''''YES'''' button at the bottom of the screen. Thanks, Gary Brown "charlie" wrote: I need a formula that will subtract March from June returning only the number of months. For example: 6/1/08-3/1/08=3 6/1/08-3/15/08=3 6/1/08-4/15/08=2 6/1/08-5/15/08=1 However, 6/1/08-6/1/08 also need to = 1 What I have right now is 6/1/08 in cell B2 and the date to subtract in cell B7 with this formula: SUM(MONTH(B2)-MONTH(B7)) I get the result that I need with all of the above except for when using 6/1/08 in cell B7. How can I modify the formula so that any time that date is used it will also return a 1? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
Excel Formula to calulate number of days passed from date to date | Excel Discussion (Misc queries) | |||
Date Function formula that will return the date of a specific week | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) |