Finding previous year

[thakkarvamar]

Hi

I have current year endings for a few companies in yyyymm format (eg. 201103 for March 2011). I also have number of months in each company's accounting years for last 2 years. (Number of months may vary over time).

Based on this data i want to find out previous year endings in the same date format.
Sample data and output
Latest means current year and latest1 means previous year and so on

Company Latest Latest1 Latest2
A 201103
B 201103
C 201103
D 201103
E 201103
F 201106
G 201106
H 201106

NO OF MONTHS
Company LATEST LASTEST1
A 12 12
B 9 15
C 12 15
D 6 15
E 15 12
F 18 12
G 12 12
H 12 12


OUTPUT NEEDED
Company Latest Latest1 Latest2
A 201103 201003 200903
B 201103 201006 200903
C 201103 201003 200812
D 201103 201009 200906
E 201103 200912 200812
F 201106 200912 200812
G 201106 201006 200906
H 201106 201006 200906


How do I do this using formulae?

 

[jevi]

=IF(DATEDIF(D8,E8,"y"),DATEDIF(D8,E8,"y")&" Years, ","")&IF(DATEDIF(D8,E8,"ym"),DATEDIF(D8,E8,"ym")&" Months, ","")&DATEDIF(D8,E8,"md")&" Days"

I don't know how helpful it might be but i use this formula to find out the age let's say: Opening date 31.03.2009 and end date 29.05.2011 so with the above formula it gives the diference in , years, months and days.

 

[thakkarvamar]

=IF(DATEDIF(D8,E8,"y"),DATEDIF(D8,E8,"y")&" Years, ","")&IF(DATEDIF(D8,E8,"ym"),DATEDIF(D8,E8,"ym")&" Months, ","")&DATEDIF(D8,E8,"md")&" Days"

I don't know how helpful it might be but i use this formula to find out the age let's say: Opening date 31.03.2009 and end date 29.05.2011 so with the above formula it gives the diference in , years, months and days.

Thanks Jevi for the reply. My data is not in a standard date format so I doubt if it works that ways

I have managed to crack the year part .. dont know how to do the month part

=QUOTIENT((VALUE(LEFT(H13,4))*12+VALUE(RIGHT(H13,2))-I13),12)

H13 has the current year and I13 has the number of months .. So here I am finding the total number of months from the year 0000 ie 2011 * 12 + 03, then subtracting months (say 18) and dividing by 12 ..
It gives me the previous year..

If only there is a way to return the remainder then my problem will be solved by concatenating..

 

[number kruncher]

Try..

=TEXT(DATE(LEFT(B2,4),MONTH(RIGHT(B2,2)*30)-SUMIF($A$15:A22,$A2,B$15:B$22),DAY(1)),"yyyymm")

B2 being first Date in Latest Column, and A15:C22 being Month durations in a simple table

 

[thakkarvamar]

Try..

=TEXT(DATE(LEFT(B2,4),MONTH(RIGHT(B2,2)*30)-SUMIF($A$15:A22,$A2,B$15:B$22),DAY(1)),"yyyymm")

B2 being first Date in Latest Column, and A15:C22 being Month durations in a simple table

Thanks for the solution but I did not understand it ..

Managed it by

=VALUE(CONCATENATE(QUOTIENT((VALUE(LEFT(H13,4))*12+VALUE(RIGHT(H13,2))-I13),12),IF(MOD((VALUE(LEFT(H13,4))*12+VALUE(RIGHT(H13,2))-I13),12)>9,MOD((VALUE(LEFT(H13,4))*12+VALUE(RIGHT(H13,2))-I13),12),CONCATENATE("0",MOD((VALUE(LEFT(H13,4))*12+VALUE(RIGHT(H13,2))-I13),12)))))