Skip to main content

Conas an dáta a thiontú go bliain fhioscach / ráithe / mí in Excel?

Má tá liosta dátaí agat i mbileog oibre, agus gur mhaith leat bliain fhioscach / ráithe / mí na ndátaí seo a dhearbhú go tapa, is féidir leat an rang teagaisc seo a léamh, sílim go bhfaighidh tú an réiteach.

Dáta a thiontú go bliain fhioscach

Dáta a thiontú go ráithe fioscach

Dáta a thiontú go mí fioscach

mboilgeog cheart gorm saighead Dáta a thiontú go bliain fhioscach

1. Roghnaigh cill, agus clóscríobh uimhir mhí na bliana fioscach ag tosú inti, anseo, tosaíonn bliain fhioscach mo chuideachta ón 1 Iúil, agus clóscríobhaim 7. Féach ar an scáileán:
doc-convert-fioscach-bliain-1

2. Ansin is féidir leat an fhoirmle seo a chlóscríobh =YEAR(DATE(YEAR(A4),MONTH(A4)+($D$1-1),1)) isteach i gcill in aice le do dhátaí, ansin tarraing an láimhseáil líonta chuig raon atá uait.doc-convert-fioscach-bliain-2

Leid: San fhoirmle thuas, léiríonn A4 an chill dáta, agus léiríonn D1 an mhí a dtosaíonn an bhliain fhioscach.

mboilgeog cheart gorm saighead Dáta a thiontú go ráithe fioscach

Más mian leat an dáta a thiontú go ráithe fioscach, is féidir leat iad seo a dhéanamh:

1. Ar dtús, ní mór duit tábla a dhéanamh mar atá thíos den ghrianghraf a thaispeántar. Sa chéad liosta liosta gach mí de bhliain, ansin sa dara sraith, clóscríobh an uimhir ráithe fioscach choibhneasta le gach mí. Féach an pictiúr:
doc-convert-fioscach-bliain-3

2. Ansin i gcill in aice le do cholún dáta, agus clóscríobh an fhoirmle seo = ROGHA (MÍ (A6), 3,3,3,4,4,4,1,1,1,2,2,2) isteach ann, ansin tarraing an láimhseáil líonta chuig raon atá uait.doc-convert-fioscach-bliain-4

Leid: San fhoirmle thuas, is é A6 an chill dáta, agus is í an tsraithuimhir 3,3,3… an tsraith ráithe fioscach a chlóscríobh tú i gcéim 1.

mboilgeog cheart gorm saighead Dáta a thiontú go mí fioscach

Chun an dáta a thiontú go mí fioscach, ní mór duit tábla a dhéanamh ar dtús.

1. Sa chéad liosta liosta gach mí de bhliain, ansin sa dara sraith, clóscríobh uimhir choibhneasta na míosa fioscaí gach mí. Féach an pictiúr:doc-convert-fioscach-bliain-5

2. Ansin i gcill in aice leis an gcolún, clóscríobh an fhoirmle seo = ROGHA (MÍ (A6), 7,8,9,10,11,12,1,2,3,4,5,6) isteach ann, agus tarraing an láimhseáil líonta chuig do raon riachtanach leis an bhfoirmle seo. doc-convert-fioscach-bliain-6

Leid: San fhoirmle thuas, is é A6 an chill dáta, agus is í an tsraithuimhir 7,8,9… an tsraith uimhreacha mí fioscach a chlóscríobhann tú i gcéim 1.

Déan an dáta neamhchaighdeánach a thiontú go tapa go formáidiú dáta caighdeánach (mm / dd / bbbb)
Uaireanta, b’fhéidir go bhfaighidh tú siopaí oibre le dátaí neamhchaighdeánacha iomadúla, agus gach ceann acu a thiontú go formáidiú an dáta chaighdeánaigh mar mm / dd / bbbb b’fhéidir go mbeadh sé trioblóideach duit. Seo Kutools le haghaidh Excel's Cóineasú go dtí seo in ann na dátaí neamhchaighdeánacha seo a thiontú go formáidiú dáta caighdeánach le cliceáil amháin.  Cliceáil le haghaidh trialach iomlán saor in aisce i 30 lá!
dáta tiontaithe doc
 
Kutools for Excel: le níos mó ná 300 breiseán áisiúil Excel, saor in aisce le triail gan aon teorannú i 30 lá.

Uirlisí Táirgiúlachta Oifige is Fearr

🤖 Kutools AI Aide: anailís sonraí a réabhlóidiú bunaithe ar: Forghníomhú Chliste   |  Gin Cód  |  Cruthaigh Foirmlí Saincheaptha  |  Anailís a dhéanamh ar Sonraí agus Cairteacha a Ghin  |  Feidhmeanna Kutools a agairt...
Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup  |   VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle   |  Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla   |  Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí:  12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

Tuairisc

Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
Comments (27)
No ratings yet. Be the first to rate!
Sopan
Sopan
This comment was minimized by the moderator on the site
How to set If formula is target is month i.e 07.2023 , 08.2022 ...
Reply
0
0
Sopan
Sopan     Sopan
This comment was minimized by the moderator on the site
For Find in FY Year by month
Reply
0
0
Abel
Abel
This comment was minimized by the moderator on the site
Hi, I would like to get an OUTPUT as a Date(10/11/2022) in Colum F, from a Fiscal Year (2023) in column A and Month(11) in Colum D. As my fiscal Year starts from November and Ends in October.

Appreciate your help to find a formula in Excel. Thank
Reply
0
0
Punit
Punit
This comment was minimized by the moderator on the site
=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 </div>;
Reply
0
0
Sathish
Sathish
This comment was minimized by the moderator on the site
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
Reply
0
0
raghu
raghu     Sathish
This comment was minimized by the moderator on the site
worked well...thank you.
Reply
0
0
Sathish
Sathish
This comment was minimized by the moderator on the site
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 <span style="letter-spacing: 0.2px; color: inherit; font-family: inherit; font-style: inherit; font-variant-ligatures: inherit; font-variant-caps: inherit; font-weight: inherit;"> </span>
Sathish
Reply
0
0
Fraser
Fraser
This comment was minimized by the moderator on the site
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.)
Reply
0
0
Cody
Cody     Fraser
This comment was minimized by the moderator on the site
"=Vlookup(Month(A6),etc.)" It's so simple, yet so elegant... not sure why I didn't think of this but this was a huge find. Thank you!
Reply
0
0
Ron
Ron
This comment was minimized by the moderator on the site
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 ...
Reply
0
0
Bret Sutton
Bret Sutton
This comment was minimized by the moderator on the site
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)
Reply
0
0
Jay Joshi
Jay Joshi
This comment was minimized by the moderator on the site
IF YOU WANT TO FIND FISCAL YEAR IN INDIA....
WRITE EXCEL QUERY AS BELOW


=(YEAR(DATE)+(MONTH(DATE)>=4))-1
Reply
0
0
Nakul Dahiya
Nakul Dahiya
This comment was minimized by the moderator on the site
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"))
Reply
0
0
Sanjay Dangi
Sanjay Dangi     Nakul Dahiya
This comment was minimized by the moderator on the site
You can also use below formula for the same condition :


=IF(MONTH(K2)<4,YEAR(K2)-1&"-"&TEXT(K2,"YY"),YEAR(K2)&"-"&TEXT(K2,"YY")+1)
Reply
0
0
Vinod
Vinod     Nakul Dahiya
This comment was minimized by the moderator on the site
Thank you!!
Reply
0
0
ClaireH
ClaireH     Nakul Dahiya
This comment was minimized by the moderator on the site
What a legend. Thank you!
Reply
0
0
Ravi
Ravi     Nakul Dahiya
This comment was minimized by the moderator on the site
thank you
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations