Excel: Conversión de Fechas a Trimestres
Excel tiene funciones integradas para devolver año, número de mes, nombre de mes, número de semana, día de la semana o día desde cualquier celda que contenga una fecha. Pero no hay una forma incorporada de devolver un cuarto de número. Los trimestres son más difíciles porque la definición de trimestre varía según la fecha de finalización del año fiscal.
PARA AÑOS CALENDARIO
Si su año financiero termina el 31 de diciembre, puede usar una fórmula corta. (La figura 1 muestra las funciones de Excel que puede usar para convertir una fecha en años, meses, semanas o días de la semana.) Primero, use la función = MONTH () para convertir la fecha en un número de mes. Divida ese resultado por 3. Las fechas de enero volverán a 0.33, 0.67 de febrero, 1.00 de marzo, 1.33 de abril, y así sucesivamente. Al usar la función = ROUNDUP (), tendrá 1 para enero, Febrero y Marzo; 2 para abril a junio; y así sucesivamente.
Para mostrar el trimestre como «Q1» o «Qtr 1», concatene el texto apropiado antes del resultado. En la Figura 2, la celda A7 utiliza una fórmula de = «Q» &ROUNDUP (MES (A2)/3,0)&»-«&AÑO (A2) para volver «Q1-2017.»
PARA OTROS EJERCICIOS FISCALES
Los ejercicios fiscales que terminan el 31 de enero son comunes en las empresas minoristas. Los contratistas del gobierno podrían utilizar un año fiscal a finales del 30 de septiembre. Para calcular un trimestre fiscal, primero convierta la fecha en un número de mes usando =MONTH(). Este resultado está limitado a 12 enteros de 1 a 12.
Puede usar una fórmula de VLOOKUP para convertir el número de mes en el número de trimestre. Pero la fórmula =VLOOKUP (MES (A2),{1,4;2,1;5,2;8,3;11,4},2) es difícil de recordar y escribir. La constante de matriz dentro de las llaves contiene una tabla de búsqueda de 5 filas por 2 columnas.
En su lugar, la función = CHOOSE () le permitiría especificar 12 números de trimestre que corresponden a los meses del 1 al 12. Si su año fiscal termina en enero, usaría =CHOOSE(MONTH (A2),4,1,1,1,2,2,2,3,3,3,4,4). Para un año fiscal que termina en marzo, use = CHOOSE (MES (A2),4,4,4,1,1,1,2,2,2,3,3,3).
Si prefiere mostrar el trimestre como Q1 en lugar de 1, puede usar el ampersand para concatenar una «Q» antes de la función CHOOSE. Los resultados se muestran en las celdas A13 y A20 de la Figura 2, con las fórmulas que se muestran en D13 y D20.
ANEXANDO UN AÑO FISCAL AL TRIMESTRE
En algunas empresas, el año fiscal se determina por el año calendario en el que se produce el 12º mes del año. Por ejemplo, un año fiscal que va de febrero de 2017 a enero de 2018 tendría trimestres llamados del primer trimestre de 2018 al cuarto trimestre de 2018. En otras empresas, el año fiscal podría denominarse algo así como » 17-18.»
Para el caso en que el año fiscal sea el año del mes final, utilice una función IF() para comprobar si el número de mes es menor que el mes inicial. Por ejemplo, con un año fiscal que termina en enero, el año fiscal será = AÑO(A2) si la fecha es anterior a febrero y =AÑO (A2)+1 en febrero y posteriores. Por lo tanto, para incluir el año fiscal con el trimestre, podría usar =IF(MES(A2)<2,0,1)+AÑO(A2).
Para un año que termine en marzo, cambiaría la prueba <2 a < 4: = IF (MES (A2)<4,0,1) + AÑO (A2). Para un año que finaliza en septiembre, se probaría < 10: = IF (MES(A2)<10,0,1)+AÑO (A2).
La celda A21 en la Figura 2 muestra cómo concatenar el trimestre fiscal y el año juntos usando esta fórmula larga: =»Q»&CHOOSE(MONTH(A17),4,1,1,1,2,2,2,3,3,3,4,4)&»-«&IF(MONTH(A17)<2,0,1)+YEAR(A17). Eso devolvería el trimestre en un formato de «Q4-2017.»
Si, en cambio, lo necesita para decir 17-18, la fórmula larga se vuelve aún más larga. Solo para obtener la porción anual de la fórmula, usaría: =IF(MONTH(A2)<4,MOD(YEAR(A2)-1,100)&»-«&TEXT(A2,»YY»),TEXT(A2,»YY»)&»-«&MOD(YEAR(A2)+1,100)).
DEVOLVIENDO UN PERÍODO EN LUGAR DE UN MES
Recientemente realicé un seminario para IMA ® en Nashville. Debido a que el gobierno del estado de Tennessee tiene un año fiscal que termina en junio, prefirieron informar usando un número de período en lugar de un mes. Julio es el período 1, diciembre es el período 6, enero es el período 7, y así sucesivamente. En este caso, muchos de los compañeros de trabajo usaron una tabla de VLOOKUP en el lado derecho de los datos. Pero también podrían usar =CHOOSE (MES (A2),7,8,9,10,11,12,1,2,3,4,5,6) para lograr el mismo resultado.
LAS FECHAS NO PUEDEN SER DE TEXTO
La advertencia para todas estas fórmulas es que está almacenando sus fechas como fechas reales y no como texto. Una forma rápida de comprobarlo: Presione Ctrl y la tecla de acento grave (`) para cambiar al modo Mostrar fórmulas. Las fechas reales se convertirán en un número de serie como 42783. Si aún ve una fecha de texto, las fórmulas no funcionarán. Presione Ctrl` ‘ de nuevo para salir de Mostrar fórmulas.
Recientemente encontré una hoja de trabajo en la que el gerente quería que las fechas se mostraran como el 1 de febrero de 2017, el 2 de febrero de 2017, el 3 de febrero de 2017 y el 4 de febrero de 2017. Los sufijos después de la fecha («st», «nd», «rd» y «th») convierten un número cardinal en un número ordinal. Si bien el Controlador de relleno de Excel le permitirá crear una columna de números cardinales fácilmente, las fórmulas de este artículo se vuelven mucho más complejas. Una fórmula de 41 caracteres se convirtió en 2.438 caracteres para manejar las fechas de texto (ver Figura 3).
