If you want to subtract Years from a given date, formulas would be -
=EDATE(A1,-12*B1)
=DATE(YEAR(A1)-B1,MONTH(A1),DAY(A1))
8. Convert a Number to a Month Name
Use below formula to generate named 3 lettered month like Jan, Feb....Dec
=TEXT(A1*30,"mmm")
Replace "mmm" with "mmmm" to generate full name of the month like January,
February....December in any of the formulas in this post.
9. Converting Date to a Calendar Quarter
Assuming date is in Cell A1. You want to convert it into a quarter (1, 2, 3 & 4). Jan to Mar is
1, Apr to Jun is 2, Jul to Sep is 3 and Oct to Dec is 4.
=CEILING(MONTH(A1)/3,1)
OR
= ROUNDUP(MONTH(A1)/3,0)
OR
=CHOOSE(MONTH(A1),1,1,1,2,2,2,3,3,3, 4,4,4)
10. Converting Date to a Indian Financial Year Quarter
Assuming date is in Cell A1. You want to convert it into a Indian Financial Year Quarter. Jan
to Mar is 4, Apr to Jun is 1, Jul to Sep is 2 and Oct to Dec is 3.
=CEILING(MONTH(A1)/3,1)+IF(MONTH(A1)<=3,3,-1)
OR
=ROUNDUP(MONTH(A1)/3,0)+IF(MONTH(A1)<=3,3,-1)
OR
=CHOOSE(MONTH(A1),4,4,4,1,1,1,2,2,2,3,3,3)
