Hello! Does anyone know a way that I can use a formula to convert a year to a decade? Forgive me if this is simple, I am new to formulas… Thanks!
Solved
Change year to decade
+12Best answer by Ilan_Ben_Yaakov
this worked for me
CONCATENATE(FLOOR(year,10)-FLOOR(year,100),"s")
+12I believe it’s something like
FLOOR(YEAR(Date),10)
Thanks, I’ll try that. Where exactly would I put in the target field that has the date in it?
+7Thanks, I’ll try that. Where exactly would I put in the target field that has the date in it?
my formula takes a date, extract the year, and returns the decade.
If you already have the year try
FLOOR(year,10)
+12I believe it’s something like
FLOOR(YEAR(Date),10)
Ok, it does in fact work. Maybe you can help me with one more thing. As it is now, it shows me the result as a 4 digit year, for example 1982 or 1983 will both return 1980 for the decade. Is there a way to make the result show as just the last two digits? like “80s” or “90s” ?
Thanks again
+12this worked for me
CONCATENATE(FLOOR(year,10)-FLOOR(year,100),"s")
Thats wonderful, thank you so much! Last one but not a big deal. This returns everything perfectly like "80s, 90s) etc. except for the 2000s which just comes back as “0s”… any ideas there?
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.