Note: The other languages of the website are Google-translated. Back to English
Vpiši se  \/ 
x
or
x
Registracija  \/ 
x

or

Kako pretvoriti datum v proračunsko leto / četrtletje / mesec v Excelu?

Če imate na delovnem listu seznam datumov in želite hitro potrditi poslovno leto / četrtletje / mesec teh datumov, lahko preberete to vadnico, mislim, da boste morda našli rešitev.

Pretvori datum v proračunsko leto

Pretvori datum v davčno četrtletje

Pretvori datum v davčni mesec

puščica modri desni mehurček Pretvori datum v proračunsko leto

1. Izberite celico in vanjo vnesite številko fiskalnega leta, ki se začne z mesecem, tukaj se fiskalno leto mojega podjetja začne 1. julija in vtipkam 7. Oglejte si posnetek zaslona:
doc-convert-fiskalno-leto-1

2. Nato lahko vnesete to formulo =YEAR(DATE(YEAR(A4),MONTH(A4)+($D$1-1),1)) v celico poleg vaših datumov, nato povlecite ročico za polnjenje v obseg, ki ga potrebujete.doc-convert-fiskalno-leto-2

Nasvet: V zgornji formuli A4 označuje datumsko celico, D1 pa mesec, v katerem se začne proračunsko leto.

puščica modri desni mehurček Pretvori datum v davčno četrtletje

Če želite datum pretvoriti v davčno četrtletje, lahko storite naslednje:

1. Najprej morate narediti tabelo, kot je prikazano spodaj. V prvo vrstico naštejte vse mesece v letu, nato v drugo vrstico vnesite relativno številko davčnega četrtletja za vsak mesec. Oglejte si posnetek zaslona:
doc-convert-fiskalno-leto-3

2. Nato v celico poleg stolpca z datumom vnesite to formulo = IZBERI (MESEC (A6), 3,3,3,4,4,4,1,1,1,2,2,2) vanj, nato povlecite ročico za polnjenje v obseg, ki ga potrebujete.doc-convert-fiskalno-leto-4

Nasvet: V zgornji formuli je A6 datumska celica, številčna serija 3,3,3… pa fiskalna četrtinska serija, ki ste jo vnesli v 1. koraku.

puščica modri desni mehurček Pretvori datum v davčni mesec

Če želite datum pretvoriti v davčni mesec, morate najprej sestaviti tudi tabelo.

1. V prvo vrstico vnesite vse mesece v letu, nato v drugo vrstico vnesite relativno številko davčnega meseca za vsak mesec. Oglejte si posnetek zaslona:doc-convert-fiskalno-leto-5

2. Nato v celico poleg stolpca vnesite to formulo = IZBERI (MESEC (A6), 7,8,9,10,11,12,1,2,3,4,5,6) vanj in s to formulo povlecite ročico za polnjenje do želenega obsega. doc-convert-fiskalno-leto-6

Nasvet: V zgornji formuli je A6 datumska celica, številčna serija 7,8,9… pa številčna številka davčnega meseca, ki jo vnesete v 1. koraku.

Hitro pretvorite nestandardni datum v standardno oblikovanje datuma (mm / dd / llll)
Včasih boste morda prejeli delovne liste z več nestandardnimi datumi in če jih boste pretvorili v standardno obliko zapisa datuma, saj je mm / dd / llll morda za vas težavno. Tukaj Kutools za Excel's Pretvori v datum lahko te nestandardne datume z enim klikom hitro pretvori v standardno oblikovanje datuma.  Kliknite brezplačno popolno predstavitev v 30 dneh!
datum pretvorbe doc
 
Kutools za Excel: z več kot 300 priročnimi dodatki za Excel lahko brezplačno preizkusite brez omejitev v 30 dneh.

Najboljša orodja za pisarniško produktivnost

Kutools za Excel rešuje večino vaših težav in poveča produktivnost za 80%

  • Ponovna uporaba: Hitro vstavite zapletene formule, grafikoni in vse, kar ste že uporabljali; Šifriraj celice z geslom; Ustvari poštni seznam in pošiljanje e-pošte ...
  • Vrstica Super Formula (enostavno urejanje več vrstic besedila in formule); Bralna postavitev (enostavno branje in urejanje velikega števila celic); Prilepite v filtrirani obseg...
  • Združi celice / vrstice / stolpce brez izgube podatkov; Vsebina razdeljenih celic; Združi podvojene vrstice / stolpce... prepreči podvojene celice; Primerjaj obsege...
  • Izberite Duplicate ali Unique Vrstice; Izberite prazne vrstice (vse celice so prazne); Super Find in Fuzzy Find v mnogih delovnih zvezkih; Naključna izbira ...
  • Natančna kopija Več celic brez spreminjanja sklica formule; Samodejno ustvarjanje referenc na več listov; Vstavi oznake, Potrditvena polja in še več ...
  • Izvleček besedila, Dodaj besedilo, Odstrani po položaju, Odstrani presledek; Ustvari in natisni vmesne seštevke strani Pretvarjanje med vsebino celic in komentarji...
  • Super filter (shranite in uporabite sheme filtrov za druge liste); Napredno razvrščanje glede na mesec / teden / dan, pogostost in drugo; Poseben filter s krepko, ležeče ...
  • Združite delovne zvezke in delovne liste; Spoji tabele na podlagi ključnih stolpcev; Razdelite podatke na več listov; Paketna pretvorba xls, xlsx in PDF...
  • Več kot 300 zmogljivih funkcij. Podpira Office / Excel 2007-2019 in 365. Podpira vse jezike. Preprosta namestitev v vašem podjetju ali organizaciji. Vse funkcije 30-dnevnega brezplačnega preskusa. 60-dnevno jamstvo za vračilo denarja.
zavihek kte 201905

Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50% in vsak dan zmanjša na stotine klikov z miško!
dno pisarniške mize
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 · 23 days 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 · 1 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.
    Sathish · 1 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)