Yuav ua li cas Hloov Hnub Hnub Ua Haujlwm hauv Excel

2024 Tus sau: Abigail Brown | [email protected]. Kawg hloov kho: 2024-01-07 19:03

Ib qho zoo ntawm Microsoft Excel yog feem ntau muaj ntau tshaj li ib txoj hauv kev los ua ntau yam haujlwm nrov, suav nrog cov qauv hnub. Txawm hais tias koj tau xa cov ntaub ntawv los ntawm lwm daim ntawv tshaj tawm lossis cov ntaub ntawv, lossis tsuas yog nkag mus rau cov hnub kawg rau koj cov nqi them txhua hli, Excel tuaj yeem yooj yim format hnub feem ntau.

Cov lus qhia hauv kab lus no siv rau Excel rau Microsoft 365, Excel 2019, 2016, thiab 2013.

Yuav ua li cas hloov Excel Hnub Format Ntawm Lub Hom Phiaj Cells Feature

Nrog rau kev siv Excel ntau cov ntawv qhia zaub mov, koj tuaj yeem hloov kho hnub tim hom li ob peb clicks.

1. Xaiv lub Home tab.

2. Nyob hauv pab pawg Cells, xaiv Format thiab xaiv Format Cells.

   

3. Nyob hauv qab tus lej tab nyob rau hauv Format Cells dialog, xaiv Date.

   

4. Raws li koj tuaj yeem pom, muaj ntau yam kev xaiv rau formatting hauv lub thawv Hom.

   

   Koj tseem tuaj yeem saib hauv Locale (qhov chaw) poob qis los xaiv hom ntawv zoo tshaj plaws rau lub tebchaws koj sau rau.

5. Thaum koj tau txiav txim siab rau ib hom ntawv, xaiv OK txhawm rau hloov hnub hom ntawv ntawm tes xaiv hauv koj daim ntawv Excel.

Ua rau koj tus kheej nrog Excel Custom Date Format

Yog tias koj tsis pom hom ntawv koj xav siv, xaiv Custom nyob rau hauv qeb qeb los tsim hnub koj xav li cas. Hauv qab no yog qee cov ntawv luv uas koj yuav xav tau los tsim kom tau ib qho kev hloov kho hnub tim.

Cov ntawv luv siv hauv Excel rau hnub
hli pom li 1-12	m
hli pom li 01-12	mm
Hnub qhia li Jan-Dec	mmm
Lub Ib Hlis Lub Ib Hlis-Lub Kaum Ob Hlis	mmmm
hli qhia raws li thawj tsab ntawv ntawm lub hli	mmmmm
Hnub (1-31)	d
Hnub (01-31)	dd
Hnub (Sun-Sat)	ddd
Hnub (Sunday-Saturday)	dddd
xyoo (00-99)	Yyy
xyoo (1900-9999)	yyyy

1. Xaiv lub Home tab.

2. Nyob rau hauv pawg Cells, xaiv qhov Format poob qis, ces xaiv Format Cells.

   

3. Nyob rau hauv tus lej tab nyob rau hauv hom Cells dialog, xaiv Custom. Ib yam li Pawg Hnub, muaj ob peb hom kev xaiv.

   

4. Thaum koj tau txiav txim siab rau ib hom ntawv, xaiv OK txhawm rau hloov hnub hom ntawv rau cov xov tooj uas tau xaiv hauv koj daim ntawv Excel.

How to Format Cells using a Mouse

Yog tias koj xav siv koj tus nas nkaus xwb thiab xav kom tsis txhob ua ntau yam ntawv qhia zaub mov, koj tuaj yeem hloov hnub hom ntawv nrog rau txoj cai-nias cov ntawv qhia zaub mov hauv Excel.

1. Xaiv lub xovtooj ntawm cov hnub uas koj xav hloov pauv hom.

2. Right-click the selection and select Format Cells. Xwb, nias Ctrl+1 txhawm rau qhib lub Format Cells dialog.

   

   Xwb, xaiv Home > Number, xaiv tus arrow, ces xaivNumber Format nyob rau hauv qab sab xis ntawm pab pawg. Lossis, hauv pawg Number , koj tuaj yeem xaiv lub npov poob qis, tom qab ntawd xaiv Ntau Tus lej Formats.

3. Xaiv Hnub, lossis, yog tias koj xav tau ntau hom kev kho, xaiv Custom.

   

4. Nyob rau hauv hom teb, xaiv qhov kev xaiv uas haum rau koj cov kev xav tau formatting. Qhov no yuav siv sij hawm me ntsis ntawm kev sim thiab ua yuam kev kom tau txais kev formatting.

5. Xaiv OK thaum koj tau xaiv koj hnub hom.

   Txawm hais tias siv Hnub Tim lossis Kev Cai Lij Choj, yog tias koj pom ib qho ntawm Hom nrog lub hnub qub () hom ntawv no yuav hloov pauv nyob ntawm thaj chaw (qhov chaw) koj tau xaiv.

Siv nrawm thov rau hnub ntev lossis luv

Yog tias koj xav tau kev hloov pauv sai sai los ntawm hnub luv (mm/dd/yyyy) lossis Long Date (ddd, mmmm dd, yyyy lossis Monday, Lub Ib Hlis 1, 2019), nws muaj txoj hauv kev hloov pauv sai Qhov no hauv Excel Ribbon.

1. Xaiv lub xovtooj uas koj xav hloov hnub hom.
2. Xaiv Home.

3. Nyob hauv tus lej, xaiv cov ntawv qhia zaub mov, tom qab ntawd xaiv ib qho Short Date lossis Long Date.

   

Siv cov qauv TEXT los ua cov hnub

Cov mis no yog qhov kev xaiv zoo tshaj yog tias koj xav tau khaws koj cov hnub qub hnub qub. Siv TEXT, koj tuaj yeem hais cov hom ntawv hauv lwm lub hlwb hauv txhua hom ntawv uas xav tau.

Txhawm rau pib nrog TEXT formula, mus rau lwm lub xov tooj, tom qab ntawd nkag mus rau hauv qab no los hloov cov hom:

=TEXT(, “format abbreviations”)
yog daim ntawv lo ntawm tes, thiab cov ntawv luv luv yog cov uas tau teev tseg saum toj no hauv qab Custom ntu. Piv txwv li,=TEXT(A2, “mm/dd/yyyy”) displays as 1900-01-01.






Siv Nrhiav & Hloov rau Cov Hnub Tim
Txoj kev no yog siv tau zoo tshaj yog tias koj xav hloov cov hom ntawv los ntawm dashes (-), slashes (/), lossis lub sijhawm (.) cais lub hli, hnub, thiab xyoo. Qhov no yog qhov tshwj xeeb tshaj yog tias koj yuav tsum tau hloov ntau lub hnub.

Xaiv lub xovtooj ntawm tes koj yuav tsum hloov lub hnub hom rau.

Select Home > Nrhiav & Xaiv > Replace.







Nyob hauv Nrhiav dab tsi teb, nkag mus rau koj thawj hnub sib cais (dash, hlais, lossis lub sijhawm).







Nyob hauv Hloov nrog teb, nkag mus rau yam koj xav hloov hom ntawv cais rau (dash, hlais, lossis lub sijhawm).







Cia li xaiv ib qho ntawm cov hauv qab no:

Hloov Tag Nrho: Uas yuav hloov tag nrho thawj qhov chaw nkag thiab hloov nws nrog koj xaiv los ntawm Hloov nrog teb.
Replace: Hloov thawj qhov piv txwv nkaus xwb.
Nrhiav Tag Nrho: Tsuas pom tag nrho cov thawj nkag hauv Nrhiav dab tsi teb.
Nrhiav Tom ntej: Tsuas pom qhov piv txwv tom ntej ntawm koj qhov nkag hauv Nrhiav dab tsi teb.




Siv cov ntawv rau kab los hloov mus rau hnub hom
Yog tias koj muaj koj cov hnub formatted raws li txoj hlua ntawm cov lej thiab cov qauv ntawm tes tau teeb tsa rau cov ntawv nyeem, Cov ntawv rau kab tuaj yeem pab koj hloov cov hlua ntawm cov lej mus rau ntau hom hnub pom zoo.

Xaiv lub xovtooj uas koj xav hloov hnub hom.

Ua kom lawv tau formatted li Text. (Nias Ctrl+1 txhawm rau txheeb xyuas lawv hom).







Xaiv lub Data tab.







In the Data Tools group, xaiv Text to Columns.







Xaiv yog Delimited los yog Txhim qhov dav, ces xaiv Tom ntej.





Feem ntau ntawm lub sijhawm, Delimited yuav tsum xaiv, raws li hnub ntev tuaj yeem hloov pauv.

Uncheck tag nrho cov Delimiters thiab xaiv Next.

Raws li Kem cov ntaub ntawv hom cheeb tsam, xaiv Date, xaiv hom ntawv koj cov hlua hnub uas siv cov ntawv qhia zaub mov poob, tom qab ntawd xaiv








Siv yuam kev kuaj xyuas hloov hnub hom
Yog tias koj tau xa cov hnub tim los ntawm lwm cov ntaub ntawv los yog tau nkag mus rau ob-tus lej xyoo rau hauv cov cell formatted li Text, koj yuav pom cov duab peb sab ntsuab me me nyob rau sab saum toj-sab laug ntawm lub xov tooj.
Qhov no yog Excel qhov yuam kev kuaj xyuas qhia qhov teeb meem. Vim yog qhov teeb tsa hauv Kev Tshawb Fawb Kev Ua Haujlwm, Excel yuav txheeb xyuas qhov teeb meem tshwm sim nrog ob tus lej xyoo. Txhawm rau siv Kev Tshawb Xyuas Kev Ua yuam kev los hloov koj lub hnub hom, ua cov hauv qab no:


Xaiv ib lub hlwb uas muaj qhov taw qhia. Koj yuav tsum pom lub cim exclamation nrog cov ntawv qhia zaub mov nco qab ntawm nws.







Xaiv cov ntawv qhia zaub mov thiab xaiv ib qho Hloov XX rau 19XX lossis Hloov xx rau 20XX, nyob ntawm lub xyoo nws yuav tsum ua.






Koj yuav tsum pom hnub tam sim hloov mus rau tus lej plaub.


Siv Kev Ntsuam Xyuas nrawm kom nkag mus rau hom Cells
Kev tshuaj ntsuam nrawm tuaj yeem siv rau ntau dua li formatting xim thiab style ntawm koj lub hlwb. Koj tuaj yeem siv nws kom nkag mus rau Format Cells dialog.

Xaiv ntau lub hlwb uas muaj cov hnub koj xav tau hloov pauv.

Select Quick Analysis nyob rau sab xis ntawm koj xaiv, lossis nias Ctrl+Q.







Hauv Formatting, xaiv Cov ntawv uas muaj.







Siv cov ntawv qhia zaub mov sab xis, xaiv Custom Format.







Xaiv tus Number tab, tom qab ntawd xaiv ib qho Hnub lossis Custom.







Xaiv OK ob zaug thaum ua tiav.