Date Formula

[Aurora118]

Hi,

I have got 5 columns with dates in them (30,000+ rows) and I need to calculate the number of months between the columns (1 and 2, 2 and 3 and so on), but occasionally there would be a blank cell in any given column and I would then need to calculate the number of months in the next column along that isn't blank.

I am thinking IF formulae would be too long and complicated and I do not know how to write macros.

Can anyone help please?

Thanks

 

[venkat1926]

perhaps you need a macro but there is gaps in inforamtion

1. where do you want to park col.2-col1,colr-col2 etc

2."there would be a blank cell in any given column and I would then need to calculate the number of months in the next column along that isn't blank."
not clear.
if column 3 is blank
col2-col1 ok
then what?????????

give two examples

 

[Aurora118]

Col A-D is where I have my data (dates)

Col H-L is where I have the results (the number of months between the dates)

Col A- D
Stat1, Stat2, Stat3, Stat4, Cured, CD

Col H-L
Res2, Res3, Res4, Res Cured, Res CD


I have written a formula but it does not work for all the situations:

=IF(A4="","",IF(B4="",DATEDIF(A4,C4,"m"),IF(AND(B4="",C4=""),DATEDIF(A4,D4,"m"),IF(AND(B4="",C4="",D4=""),DATEDIF(A4,E4,"m"),IF(AND(B4="",C4="",D4="",E4=""),DATEDIF(B4,F4,"m"),DATEDIF(A4,B4,"M"))))))

 

[venkat1926]

see data in A23,B2 C2 D2.
the results are in H2,I2,J2 (see formulas)
you can copy them down

there can be only three results 1 to 2,2 to 3 and 3 to 4

if the difference is less than one month as in the case B2 and C2 it will give 0
date are entered as excel dates in my region
m/d/yy
you can format them as you like

sheet is given below

remember formula is written only H2 and copied to I2 and J2
and also can be copied down if there are data in other rows.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	date1	date2	date3	date4				`datedif1	`datedif2	`datedif3
2	1/1/2011	2/8/2011	3/7/2011	4/12/2011				1	0	1
3
Sheet1