| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
ROUNDING UP monthly differences
Hi every1
I want to calculate the number of months between two dates. Datedif() may be used but upon a difference of less than 15 days it rounds off the balance to 0 months. I want to Round Up the difference upon a fractional month i.e. even 1 day difference returns a 1 month difference and 13 months and 1 day to be showing 14 months! All assistance shall highly be obliged! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
ROUNDING UP monthly differences
=DATEDIF(A1-DAY(A1)+1,DATE(YEAR(A2),MONTH(A2)+1,1),"M")
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "FARAZ QURESHI" wrote in message ... Hi every1 I want to calculate the number of months between two dates. Datedif() may be used but upon a difference of less than 15 days it rounds off the balance to 0 months. I want to Round Up the difference upon a fractional month i.e. even 1 day difference returns a 1 month difference and 13 months and 1 day to be showing 14 months! All assistance shall highly be obliged! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
ROUNDING UP monthly differences
=MAX(DATEDIF(A1,EOMONTH(B1,0),"m"),1)
"FARAZ QURESHI" wrote: Hi every1 I want to calculate the number of months between two dates. Datedif() may be used but upon a difference of less than 15 days it rounds off the balance to 0 months. I want to Round Up the difference upon a fractional month i.e. even 1 day difference returns a 1 month difference and 13 months and 1 day to be showing 14 months! All assistance shall highly be obliged! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
ROUNDING UP monthly differences
"FARAZ QURESHI" wrote:
I want to calculate the number of months between two dates. [....] I want to Round Up the difference upon a fractional month i.e. even 1 day difference returns a 1 month difference =month(a2)-month(a1)+12*(year(a2)-year(a1)) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
ROUNDING UP monthly differences
Sorry Bob
But upon a starting date of Aug 31, 07 and ending date Sep. 1 2007, i.e. a difference of only 1 day your offered formula returns the value "2" months. "Bob Phillips" wrote: =DATEDIF(A1-DAY(A1)+1,DATE(YEAR(A2),MONTH(A2)+1,1),"M") -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "FARAZ QURESHI" wrote in message ... Hi every1 I want to calculate the number of months between two dates. Datedif() may be used but upon a difference of less than 15 days it rounds off the balance to 0 months. I want to Round Up the difference upon a fractional month i.e. even 1 day difference returns a 1 month difference and 13 months and 1 day to be showing 14 months! All assistance shall highly be obliged! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
ROUNDING UP monthly differences
Sorry Mike
Upon a starting date of Aug 01, 07 I was expecting your formula to be returning a result of 1 upon the ending date being set at even Aug 02, 07 and similarly a result of 2 upon ending date of Sep. 2, 2007, i.e. a difference of 1 month and a 1 day being rounded up. However, upon even dates like (beg.) Aug 01, 2007 and (end.) Sept. 30 a difference of only 1 month is being returned by your formula instead of 2. "Mike H" wrote: =MAX(DATEDIF(A1,EOMONTH(B1,0),"m"),1) "FARAZ QURESHI" wrote Hi every1 I want to calculate the number of months between two dates. Datedif() may be used but upon a difference of less than 15 days it rounds off the balance to 0 months. I want to Round Up the difference upon a fractional month i.e. even 1 day difference returns a 1 month difference and 13 months and 1 day to be showing 14 months! All assistance shall highly be obliged! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
ROUNDING UP monthly differences
Sorry Joe
Upon a starting date of Aug 01, 07 I was expecting your formula to be returning a result of 1 upon the ending date being set at even Aug 02, 07 and similarly a result of 2 upon ending date of Sep. 2, 2007, i.e. a difference of 1 month and a 1 day being rounded up. However, upon even dates like (beg.) Aug 01, 2007 and (end.) Sept. 30 a difference of only 1 month is being returned by your formula instead of 2. Similarly Aug. 1 & Aug. 2 dates with a difference of 1 day is showing 0 months' difference. " wrote: "FARAZ QURESHI" wrote: I want to calculate the number of months between two dates. [....] I want to Round Up the difference upon a fractional month i.e. even 1 day difference returns a 1 month difference =month(a2)-month(a1)+12*(year(a2)-year(a1)) |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
ROUNDING UP monthly differences
Hey Guys,
Simple solution I found myself!!! =if(day(A1)=day(A2),datedif(a1,a2,"m"),datedif(a1, a2,"m")+1) Thanx anyway for all your help!!! "FARAZ QURESHI" wrote: Hi every1 I want to calculate the number of months between two dates. Datedif() may be used but upon a difference of less than 15 days it rounds off the balance to 0 months. I want to Round Up the difference upon a fractional month i.e. even 1 day difference returns a 1 month difference and 13 months and 1 day to be showing 14 months! All assistance shall highly be obliged! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I need a formula with rounding up & rounding down to the nearest . | Excel Worksheet Functions | |||
| differences between XL VBA for the Mac and for Windows? | Excel Discussion (Misc queries) | |||
| Rolling Monthly Amounts to Annual Monthly Amounts | Excel Discussion (Misc queries) | |||
| How do I set up monthly random work schedule for 60 hours monthly | Excel Discussion (Misc queries) | |||
| Mortgage template comparing interest pd, monthly, bi-monthly, ext. | Excel Discussion (Misc queries) | |||
Linear Mode
