Note: The other languages of the website are Google-translated. Back to English
Iniciar sesión  \/ 
x
or
x
Registrarse  \/ 
x

or

¿Cómo convertir la fecha al año fiscal / trimestre / mes en Excel?

Si tiene una lista de fechas en una hoja de trabajo y desea confirmar rápidamente el año fiscal / trimestre / mes de estas fechas, puede leer este tutorial, creo que puede encontrar la solución.

Convertir fecha en año fiscal

Convertir fecha en trimestre fiscal

Convertir fecha en mes fiscal

flecha azul burbuja derecha Convertir fecha en año fiscal

1. Seleccione una celda y escriba el número del mes de inicio del año fiscal en ella, aquí, el año fiscal de mi empresa comienza a partir del 1 de julio y escribo 7. Ver captura de pantalla:
doc-convertir-año-fiscal-1

2. Luego puede escribir esta fórmula =YEAR(DATE(YEAR(A4),MONTH(A4)+($D$1-1),1)) en una celda junto a sus fechas, luego arrastre el controlador de relleno al rango que necesite.doc-convertir-año-fiscal-2

Consejo: En la fórmula anterior, A4 indica la celda de fecha y D1 indica el mes en el que comienza el año fiscal.

flecha azul burbuja derecha Convertir fecha en trimestre fiscal

Si desea convertir la fecha en trimestre fiscal, puede hacer lo siguiente:

1. En primer lugar, debe crear una tabla como se muestra a continuación. En la primera fila, enumere todos los meses de un año, luego, en la segunda fila, escriba el número de trimestre fiscal relativo a cada mes. Ver captura de pantalla:
doc-convertir-año-fiscal-3

2. Luego, en una celda junto a la columna de la fecha y escriba esta fórmula = ELEGIR (MES (A6), 3,3,3,4,4,4,1,1,1,2,2,2) en él, luego arrastre el controlador de relleno al rango que necesite.doc-convertir-año-fiscal-4

Consejo: En la fórmula anterior, A6 es la celda de fecha y la serie numérica 3,3,3… es la serie del trimestre fiscal que escribió en el paso 1.

flecha azul burbuja derecha Convertir fecha en mes fiscal

Para convertir la fecha en el mes fiscal, también debe crear una tabla en primer lugar.

1. En la primera fila, enumere todos los meses de un año, luego, en la segunda fila, escriba el número de mes fiscal relativo a cada mes. Ver captura de pantalla:doc-convertir-año-fiscal-5

2. Luego, en una celda junto a la columna, escriba esta fórmula = ELEGIR (MES (A6), 7,8,9,10,11,12,1,2,3,4,5,6) en él y arrastre el controlador de relleno al rango necesario con esta fórmula. doc-convertir-año-fiscal-6

Consejo: En la fórmula anterior, A6 es la celda de fecha y la serie numérica 7,8,9… es la serie numérica del mes fiscal que ingresó en el paso 1.

Convierta rápidamente fecha no estándar en formato de fecha estándar (mm / dd / aaaa)
En algunas ocasiones, es posible que reciba hojas de trabajo con varias fechas no estándar, y convertirlas todas al formato de fecha estándar como mm / dd / aaaa puede resultarle problemático. aquí Kutools for Excel's Conver hasta la fecha puede convertir rápidamente estas fechas no estándar al formato de fecha estándar con un solo clic.  ¡Haga clic para obtener una prueba gratuita con todas las funciones en 30 días!
doc convertir fecha
 
Kutools para Excel: con más de 300 prácticos complementos de Excel, prueba gratis sin límite en 30 días.

Las mejores herramientas de productividad de oficina

Kutools para Excel resuelve la mayoría de sus problemas y aumenta su productividad en un 80%

  • Reutilizar: Inserte rápidamente fórmulas complejas, gráficos y cualquier cosa que hayas usado antes; Cifrar celdas con contraseña; Crear lista de distribución y enviar correos electrónicos ...
  • Barra de súper fórmula (edite fácilmente varias líneas de texto y fórmulas); Diseño de lectura (leer y editar fácilmente un gran número de celdas); Pegar en rango filtrado...
  • Combinar celdas / filas / columnas sin perder datos; Contenido de celdas divididas; Combinar filas / columnas duplicadas... Prevenir celdas duplicadas; Comparar rangos...
  • Seleccione Duplicado o Único Filas; Seleccionar filas en blanco (todas las celdas están vacías); Super Find y Fuzzy Find en muchos libros de trabajo; Selección aleatoria ...
  • Copia exacta Varias celdas sin cambiar la referencia de la fórmula; Crear referencias automáticamente a varias hojas; Insertar viñetas, Casillas de verificación y más ...
  • Extraer texto, Agregar texto, Eliminar por posición, Quitar espacio; Crear e imprimir subtotales de paginación; Convertir entre contenido de celdas y comentarios...
  • Súper filtro (guardar y aplicar esquemas de filtros a otras hojas); Orden avanzado por mes / semana / día, frecuencia y más; Filtro especial en negrita, cursiva ...
  • Combinar libros y hojas de trabajo; Combinar tablas basadas en columnas clave; Dividir datos en varias hojas; Conversión por lotes de xls, xlsx y PDF...
  • Más de 300 potentes funciones. Compatible con Office / Excel 2007-2019 y 365. Compatible con todos los idiomas. Fácil implementación en su empresa u organización. Características completas Prueba gratuita de 30 días. Garantía de devolución de dinero de 60 días.
pestaña kte 201905

Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil

  • Habilite la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
  • Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
  • ¡Aumenta su productividad en un 50% y reduce cientos de clics del mouse todos los días!
officetab parte inferior
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Punit · 1 months ago
    =IF(MONTH(EOMONTH(B4,0))>3,

    YEAR(B4)&"-"&RIGHT(YEAR(B4)+1,2),

    YEAR(B4)-1&"-"&RIGHT(YEAR(B4),2))

    https://excelforfinance1.blogspot.com/2021/05/excel-formula-to-get-financial-year.html 
  • To post as a guest, your comment is unpublished.
    Sathish · 2 months ago
    Just check this

    =IF(MONTH(I8)>3,"FY"&YEAR(I8)&"-"&YEAR(I8)+1,IF(MONTH(I8)<=3,"FY"&YEAR(I8)-1&"-"&YEAR(I8)))

    Assume the target is in I8 cell with a date of 8/23/2011 , the out put will be FY2011-2012

    Sathish
    • To post as a guest, your comment is unpublished.
      raghu · 8 days ago
      worked well...
      thank you.
  • To post as a guest, your comment is unpublished.
    Sathish · 2 months ago
    Just check this 

    =IF(MONTH(I8)>3,"FY"&YEAR(I8)&"-"&YEAR(I8)+1,IF(MONTH(I8)<=3,"FY"&YEAR(I8)-1&"-"&YEAR(I8)))

    Assume the target is in I8 cell with a date of 8/23/2011 , the out put will be  

    Sathish
  • To post as a guest, your comment is unpublished.
    Fraser · 1 years ago
    If anyone is looking for a formula to figure out what fiscal/policy year an payment/event happened, even if that fiscal year/policy doesn't start on the first of the month I came up with this.

    =YEAR(A4)+((MONTH(A4)>=MONTH($D$1)+(DAY(A4)<Day($D$1)))-1)

    D1 in this has to be a full date 01/06/2019 for example.

    Also surely a cheeky Vlookup is a better way of doing the quarter formula

    =Vlookup(Month(A6),etc.)
  • To post as a guest, your comment is unpublished.
    Ron · 1 years ago
    I came up with a self contained choose() to calculate the fiscal year that doesn't need to use an external cell as an input.
    .
    Fiscal Year EndCalculation
    ="June 30, " & (YEAR(E2) +CHOOSE(MONTH(E2),0,0,0,0,0,0,1,1,1,1,1,1))
    . * The first part is just a text string
    . * & concatenates the first part to the second part which calculatesthe appropriate year.
    . * Year(e2) extracts the year number from the date in column E
    . * Choose() looks at the month number from that same date.
    . For months Jan through June itretrieves the value 0,
    . For months July thru Dec itretrieves value 1.
    . The retrieved value is added to theyear extracted to give the appropriate year end
    “June 30, 2020”.

    This is on the assumption that a company is not likely to be changing it's year end. Doesn't work if the sheet is being used for multiple companies with different year ends ...
  • To post as a guest, your comment is unpublished.
    Bret Sutton · 2 years ago
    Please correct the fiscal-year formula you give above! It does not work unless the fiscal year begins with month 7. This formula will work just fine:

    =YEAR(DATE(YEAR(A4),MONTH(A4)+12-($D$1-1),1))

    But it is still way more complex than it needs to be. Use Michael's formula (but correct his typo):

    =YEAR(A4)+(MONTH(A4)>=$D$1)
  • To post as a guest, your comment is unpublished.
    Jay Joshi · 2 years ago
    IF YOU WANT TO FIND FISCAL YEAR IN INDIA....
    WRITE EXCEL QUERY AS BELOW


    =(YEAR(DATE)+(MONTH(DATE)>=4))-1
  • To post as a guest, your comment is unpublished.
    Nakul Dahiya · 3 years ago
    You can use below formulae:


    Here Col K2 is our date column which is to be converted in format 20XX-XY (e.g. 2017-18).

    =IF(MONTH(K2)<4,TEXT(DATE(YEAR(K2)-1,MONTH(K2),DAY(K2)),"YYYY-")&TEXT(DATE(YEAR(K2),MONTH(K2),DAY(K2)),"YY"),TEXT(DATE(YEAR(K2),MONTH(K2),DAY(K2)),"YYYY-")&TEXT(DATE(YEAR(K2)+1,MONTH(K2),DAY(K2)),"YY"))
    • To post as a guest, your comment is unpublished.
      Vinod · 2 years ago
      Thank you!!
    • To post as a guest, your comment is unpublished.
      ClaireH · 2 years ago
      What a legend. Thank you!
    • To post as a guest, your comment is unpublished.
      Ravi · 3 years ago
      thank you
  • To post as a guest, your comment is unpublished.
    Michael · 3 years ago
    To calculate the FISCAL YEAR, MONTH, and QUARTER of a given date (here in A4), these formulas will work for any FY starting month (here in $D$1):
    FISCAL YEAR =YEAR(A4)+(MONTH(A4)]=$D$1)
    FISCAL MONTH =MOD(MONTH(A4)-$D$1;12)+1
    FISCAL QUARTER =QUOTIENT(MOD(MONTH(A4)-$D$1;12);3)+1

    How it works (examples assume FY starting month in $D$1 = July = 7):

    A. (see also formula of MikeyMoe316 below)
    As in MS EXCEL the equivalent value for TRUE = 1 and FALSE = 0, the FISCAL YEAR can also be calculated without the IF clause as:
    FISCAL YEAR =YEAR(A4)+(MONTH(A4)]=$D$1)

    B. In the Fiscal Month formula, the innermost term [MONTH(A4) - $D$1] will shift the months by the FY starting month, i.e. JUL(7) - StartMonth(7) = 7 - 7 = 0.
    So the result for date in fiscal start month = 0, while all others range from -11..+11, e.g. Jul = 0, Aug= 1, ... , Dec = 5; Jan = -6; Feb = -5; Jun = -1

    C. Running the result [B] above through the term [MOD([B];12)], will retain the positive numbers, while increasing any negative [B] by 12.
    Now the results range from 0.11, e.g. Jul = 0, Aug= 1, ... , Dec = 5; Jan = 6; Feb = 7; Jun = 11

    D. The final [+1] term will adjust the 0..11 range of [C] to be the correct 1..12 fiscal month range, so the FiscalStartMonth = 1, et voila:
    e.g. Jul = 1, Aug= 2, ... , Dec = 6; Jan = 7; Feb = 8; Jun = 12

    E. Converting a FISCAL MONTH to a FISCAL QUARTER becomes relatively easy then, knowing that quarter (= 3 months) can be calculated by
    QUARTER = QUOTIENT( [Month] - 1 ; 3) + 1

    F. Replacing [Month] with [Fiscal Month] in above formula to get .......... QUOTIENT( [Fiscal Month] - 1 ; 3) + 1
    Now we can insert the FISCAL MONTH formula from above and get .... QUOTIENT( MOD(MONTH(A4)-$D$1;12)+1 - 1 ; 3) + 1
    The [+1 -1] in the middle can be removed, and we end up with ............. QUOTIENT( MOD(MONTH(A4)-$D$1;12) ; 3) + 1
  • To post as a guest, your comment is unpublished.
    MikeyMoe316 · 3 years ago
    In the Fiscal Year example, the formula in cell D4 should be:
    =YEAR(A4)+IF(MONTH(A4)>=$D$1,1,0)
    This will work for any fiscal year, not just one that starts with July
  • To post as a guest, your comment is unpublished.
    Edward · 4 years ago
    Hi

    Our fiscal year starts in February and ends in January of the following year (for example 1-Feb-2017 to 31-Jan-2018 should = Fiscal Year 2018, hence 31-Jul-2017 should be in Fiscal Year 2018, your formula computes Fiscal Year 2017). So I did everything as you stated above and changed the "Fiscal year starting month" to 2 and it does not calculate it right for some dates. I am not sure why. I would appreciate if you could please get back to me. Many thanks
    • To post as a guest, your comment is unpublished.
      J. Randle · 4 years ago
      [quote name="Edward"]Hi

      Our fiscal year starts in February and ends in January of the following year (for example 1-Feb-2017 to 31-Jan-2018 should = Fiscal Year 2018, hence 31-Jul-2017 should be in Fiscal Year 2018, your formula computes Fiscal Year 2017). So I did everything as you stated above and changed the "Fiscal year starting month" to 2 and it does not calculate it right for some dates. I am not sure why. I would appreciate if you could please get back to me. Many thanks[/quote]

      Would this work for you?

      =IF(MONTH(A1)>=2,YEAR(A1)+1,YEAR(A1))
      Any month >=Feb would return the current year+1 which is your FY. Otherwise, it will only return the year as is. When I did Ctrl+: in cell A1 to paste date in MS Excel, it returned 2018. Changed the 04 to a 01 and it returned 2017.
      • To post as a guest, your comment is unpublished.
        Bea Viveros · 3 years ago
        WOW so easy thanks!!
  • To post as a guest, your comment is unpublished.
    jjcasta3 · 4 years ago
    Would it be easier to do this [b]MONTH(A4)+(6)[/b] instead of [b]MONTH(A4)+($D$1-1)[/b] for the fiscal year calculations?

    Thanks for the post!
  • To post as a guest, your comment is unpublished.
    Kamran · 4 years ago
    An easy formula for Fiscal Month:
    =IF(+TEXT(Index!E2,"M")-6
  • To post as a guest, your comment is unpublished.
    charles · 4 years ago
    Please ignore my previous Comment.
  • To post as a guest, your comment is unpublished.
    charles · 4 years ago
    I believe your formula may be incorrect. If your fiscal year starts in July - the 7th month. This would indicate a new "fiscal" year. January 1 2014 & March 3rd 2014; should they not fall under Fiscal Year 2013.. Not 2014 as calculated? I am asking because my fiscal year starts in April (ends in March) and I am trying to figure out the formula. When I type in 4, it does not appear to populate correctly.
  • To post as a guest, your comment is unpublished.
    CW · 5 years ago
    I am trying to convert date to fiscal month however the CHOOSE function does not seem to calculate ? IE 30/09/2015 shows 3..

    =CHOOSE(MONTH(G4),7,8,9,10,11,12,1,2,3,4,5,6)