Hi,

I have a Spreadsheet as follows and used a DATEDIF formula to return the number of months:


01-Jan-12 29-Mar-12 3
01-Jan-12 29-Mar-12 3
01-Jan-12 29-Mar-12 3



=IF(DATEDIF(A4,B4,"m")=0,"",DATEDIF(A4,B4,"m"))+CE ILING(DATEDIF(A4,B4,"md")/30,0.5)&""

I would like to add another column which if for example todays date is within the month of January it will return the remaining months of 2 etc.

Rather copying the current formula than adding to it, would I be better off creating a fresh one in the new cell, if so what would you recommend?

If it is best to add to the current one, how could this be done?

Thanks in advance

Hi

and don't quite grasp what you want to do

EDIT BTW in your example DATEDIF is 2, not 3 I think

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Hi,

I am using this formula and it is coming out to be 3 which is correct. We are now for example in January and as we do not count any months that have started I want to calculate the remaining months from the February to the end date on the sheet.

Example we are in January so the equation would return the following results

01-Jan-12 29-Mar-12 2
01-Jan-12 29-April-12 3
01-Jan-12 29-June-12 5

Hope this is clearer.

Using your current formula as a basis, this seems to give the results you want:

Where D2 has either of these formulae which calculate the 1st day of the next month from today's date:

__________________
Colin

RAD Excel Blog

I don't get it, as Datedif counts complete months between two dates,

=Datedif(01/01/2012,29/03/2012,"m") equates to 2 for me, not 3
So =datedif(01/02/2012,29/03/2012,"m") would yield 1

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thanks Colin, but I can't seem to get them to return the results I would expect. The results they are returning are as follows:


Formula1 3
Formula2 01/02/2012
Formula3 40940

The result should in face be 2 as Jan - March is 3 months, we do not want to include the current month (Jan) so want the formula to calculate from Feb - March which would me 2 remaining months.

Your second formula is similar to what I have been trying to do but just can't seem to get it to work.

Hi Caz,

I've attached an example which implements the formulae I posted above. For 1 Jan 12 vs. 29 Mar 12 it gives the result of 2 which I believe is what you want.

Note the formula in D2 is just a helper, so that the formulae in D4 to D9 are simpler.


If it's not returning the correct results then please elaborate.

Attached Files

Book1.xls (14.5 KB, 7 views)

__________________
Colin

RAD Excel Blog

Yes I see what you've done now. Thanks very much that works a treat.