Skip to main content

Conas tiontú idir dáta Julian agus dáta féilire in Excel?

De ghnáth, is formáid dáta é dáta Julian a chuimsíonn uimhreacha 5 dhigit, léiríonn an chéad dá cheann an bhliain, agus léiríonn na trí cinn deiridh lá na bliana. Mar shampla, léiríonn 14001 1/1/2014 san fhéilire. Anois tá an rang teagaisc seo ag caint faoi na modhanna tiontaithe idir dáta Julian agus dáta féilire in Excel.

Tiontaigh dáta Julian go dáta an fhéilire

Tiontaigh dáta féilire go dáta Julian

mboilgeog cheart gorm saighead Tiontaigh dáta Julian go dáta an fhéilire

Chun dáta Julian a thiontú go dáta féilire, níl uait ach foirmle in Excel.

1. I gcill bhán, clóscríobh an fhoirmle seo =DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3)) isteach ann, brúigh Iontráil eochair, más gá duit, is féidir leat an láimhseáil líonta a tharraingt chuig raon leis an bhfoirmle seo. Féach an pictiúr:
doc-tiontú-julian-dáta-1

Leid: Léiríonn A1 an dáta Julian a chaithfidh tú a thiontú go dáta féilire.

2. Ansin cliceáil ar dheis ag na cealla aibhsithe seo, agus roghnaigh Cealla Formáid ón roghchlár comhthéacs, agus sa popped amach Cealla Formáid dialóg, faoi Uimhir tab, cliceáil dáta sa Catagóir liosta, ansin roghnaigh an cineál dáta a theastaíonn uait sa chuid cheart.
doc-tiontú-julian-dáta-2

3. cliceáil OK, ansin feiceann tú gur athraíodh gach dáta Julian go dátaí féilire.
doc-tiontú-julian-dáta-3

mboilgeog cheart gorm saighead Tiontaigh dáta féilire go dáta Julian

Más mian leat dáta féilire a thiontú go dáta Julian, is féidir leat na foirmlí thíos a úsáid.

I gcill bhán, clóscríobh an fhoirmle seo =TEXT(A1,"yy")&TEXT((A1-DATEVALUE("1/1/"&TEXT(A1,"yy"))+1),"000") agus brúigh Iontráil eochair, más gá duit is féidir leat an fhoirmle seo a chur i bhfeidhm ar raon tríd an láimhseáil líonta uathoibríoch a tharraingt.
doc-tiontú-julian-dáta-4
Leid: Is é A1 an dáta féilire is mian leat a thiontú go dáta Julian.

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á.

Ailt Choibhneasta:

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 (22)
No ratings yet. Be the first to rate!
TONY
TONY
This comment was minimized by the moderator on the site
thanks Sunny the formula worked perfect...I received it over a month ago....I appreciate it....its a big help...God Bless you
Reply
0
0
DT
DT
This comment was minimized by the moderator on the site
Can anyone help me convert a backwards formatted Julian date code? For example 25922 is September 16th, 2022.
Reply
0
0
Sunny
Sunny     DT
This comment was minimized by the moderator on the site
Hi, DT, try below formula:
=DATE(IF(0+(RIGHT(A1,2))<30,2000,1900)+RIGHT(A1,2),1,LEFT(A1,3))
then format the result as the date formatting as you need.
Reply
Report
0
0
ttatai
ttatai
This comment was minimized by the moderator on the site
Sunny-
That revised formula worked perfect! thanks for your help...I appreciate it.

Tony
Reply
Report
0
0
TONY
TONY
This comment was minimized by the moderator on the site
Sunny-
Thanks for the reply, in order to get a 4-digit number, I last digit of the year would suffice (i.e. 2022=2 or 2023=3).
If you have any other ideas on how to modify the formula let me know...and thanks again for your response.
Reply
0
0
Sunny
Sunny     TONY
This comment was minimized by the moderator on the site
Hi, Tony, try this formula =RIGHT(TEXT(I1,"yy")&TEXT((I1-DATEVALUE("1/1/"&TEXT(I1,"yy"))+1),"000"),4), I1 is the date you want to convert.
https://www.extendoffice.com/images/stories/comments/sun-comment/formula-julia-date-4-digit.png
Reply
Report
1
0
TONY
TONY
This comment was minimized by the moderator on the site
Hi-
Thank you for the excel formula...that converts a Calander date to a Julian date =TEXT(A1,"yy")&TEXT((A1-DATEVALUE("1/1/"&TEXT(A1,"yy"))+1),"000")

Your formula displays 5 digits for the Julian date....I'd like to change in the formula to get 4 digits for the Julian date. What should I do to get this outcome?

Thanks

Tony
Reply
0
0
Sunny
Sunny     TONY
This comment was minimized by the moderator on the site
Hi, Tony, the formula TEXT(A1,"yy")&TEXT((A1-DATEVALUE("1/1/"&TEXT(A1,"yy"))+1),"000") returns a 5-digit number, first two number indicate the year, last three number indicate the nth day of the year, 5 numbers combined to indicates the date. If you just want 4-digit number, I do not know which number you want to ignore.
Reply
Report
0
0
Phil
Phil     Sunny
This comment was minimized by the moderator on the site
Good Afternoon Sunny, I am in the same boat as Tony - Where I work we use a 4 digit Julian code, not a 5 digit one. For example - 29 December, 2023 would be listed in my work systems as Julian date "3363" instead of "23363" and 26 September 2016 would be listed as Julian date "6270" instead of "16270". Would it be possible for you adjust your formula to reflect this?
Reply
0
0
rgutierrezp
rgutierrezp
This comment was minimized by the moderator on the site
Si tienes Office en español, puedes utilizar la siguiente formula =TEXTO(DE2,"AA")&TEXTO((DE2-FECHANUMERO("1/1/"& TEXTO(DE2,"AA"))+1),"000") donde DE2 es la celda donde esta tu fecha gregoriana. Saludos
Reply
0
0
Ishant
Ishant
This comment was minimized by the moderator on the site
Ex: 2019181
Reply
0
0
rumesh.combank@gmail.com
    Ishant
This comment was minimized by the moderator on the site
=DATE(INT(A1/1000),1,MOD(A1,1000))
Reply
Report
0
0
Rumesh
Rumesh     Ishant
This comment was minimized by the moderator on the site
=DATE(INT(A1/1000),1,MOD(A1,1000))
Reply
0
0
Ishant
Ishant
This comment was minimized by the moderator on the site
Can anyone help me convertig 6 digital julian date to normal date ?
Reply
0
0
Cliff Johnston
Cliff Johnston
This comment was minimized by the moderator on the site
Hi, there is a problem when you try to convert Julian date to calendar date with dates of 2001 thru 2009. Any suggestions? Example 1/1/2001 = JD of 01001 which is correct. However if you use the formula JD of 01001 to Calendar Date conversion the answer is 1/1/2010. Where is this error?
Reply
0
0
Satish
Satish
This comment was minimized by the moderator on the site
Tried with formulae given in the post, but not working.
Output is as something like 2017Tue
Reply
0
0
Sunny
Sunny     Satish
This comment was minimized by the moderator on the site
Sorry I cannot get your problem clearlly? Could you upload a screenshop of your file to me for sovling? Thank u.
Reply
Report
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