A keni pasur ndonjëherë vështirësi me datat dhe orët në Excel dhe nuk keni marrë rezultatet që prisnit? Nëse analizoni të dhëna, menaxhoni projekte, ndiqni oraret ose thjesht dëshironi të mbani gjithçka të organizuar në fletëllogaritëset tuaja, ndoshta keni hasur dhimbje koke tipike kur punoni me informacione që kanë rëndësi kohore. Nga gabimet në formatim deri te llogaritjet që nuk përputhen, Të merresh me datat dhe orët në Excel mund të jetë frustruese nëse disa udhëzime thelbësore nuk janë të qarta.
Ky udhëzues gjithëpërfshirës është hartuar për t'ju ndihmuar të punoni pa gabime dhe me efikasitet maksimal.Do të analizojmë gjithçka, nga bazat deri te veçoritë e përparuara, duke përfshirë problemet e zakonshme, truket e formatimit, llogaritjet komplekse dhe rekomandimet e hollësishme.
Si i interpreton Excel datat dhe orët: parim themelor
Para se të hyjmë në detaje, duhet të kuptojmë se si Excel i sheh datat dhe orët.. Ky program ruan datat si numra serialë të njëpasnjëshëmKjo do të thotë që çdo ditë që kalon nga një datë bazë (si parazgjedhje, 1 janar 1900, në shumicën e instalimeve) korrespondon me një numër. Për shembull, numri 1 është 1/1/1900, numri 2 është 2/1/1900 e kështu me radhë. Orët, nga ana tjetër, përfaqësohen si thyesa dhjetore e ditësKështu, mesdita është 0,5 (gjysma e një dite të plotë), ora gjashtë e mbrëmjes do të ishte 0,75, etj.
Pse është kjo e rëndësishme? Sepse kur futni një datë si "25/12/2024", Excel në fakt ruan numrin e ditëve që kanë kaluar që nga viti 1900 dhe e shfaq atë si datë në formatin që zgjidhni. Nëse futni "12:00", Excel sheh numrin 0,5. Ky dualitet midis pamjes dhe të dhënave aktuale është ajo që i bën operacionet matematikore kaq të lehta për t'u kryer... dhe gjithashtu shkaku i shumë gabimeve!
Cilësimet Rajonale dhe Formatet e Datave: Gracka e Parë
Një detaj që i ndërlikon gjërat dhe shkakton shumicën e gabimeve me datat: cilësimet rajonale.Në varësi të konfigurimit të kompjuterit tuaj, Excel mund ta interpretojë një datë si DD/MM/YYYY ose MM/DD/YYYY. Kështu, "08/12/2024" mund të jetë 8 dhjetor ose 12 gusht. Kjo rrëmujë është burimi i shumë problemeve., veçanërisht kur ndani skedarë me përdorues të tjerë ose importoni të dhëna nga burime të jashtme.
Si mund ta di se çfarë formati të datës keni? shtyp Ctrl + 1 Në çdo qelizë me datë, shkoni te kategoria data dhe shikoni formatet me një yll, të cilat janë ato që varen nga cilësimet tuaja rajonale. Është thelbësore që formati të standardizohet dhe të kontrollohet GJITHMONË përpara se të filloni të kryeni llogaritjet.Nëse keni ndonjë pyetje, përdorni funksionin TEXT për të shfaqur datën në formatin e dëshiruar (do ta trajtojmë këtë më në detaje më vonë).
Funksionimi i brendshëm: numri serial dhe fraksioni i ditës
Ruajtja e datave si numra i lejon Excel-it të kryejë mbledhje, zbritje dhe të gjitha llojet e operacioneve midis datave dhe kohërave.Për shembull, për të gjetur se sa ditë ka midis dy datave, thjesht zbritini ato: “=B1-A1” do t'ju japë diferencën në ditë.
Në rastin e orëve, sistemi funksionon duke shtuar thyesën dhjetore përkatëse në numrin serial të datës.Kështu, "28/05/2024 10:12" ruhet si 45045,425. Numri i plotë është data dhe pjesa dhjetore është ora. Kjo ju lejon, për shembull, të llogaritni lehtësisht se sa orë kanë kaluar midis dy momenteve të sakta.
Futja e saktë e datave dhe orëve: çfarë mund dhe nuk mund të shkruani
Excel është mjaft fleksibël në formatet që njeh kur fut një datë ose orë… por ka kufizimet e veta.Zakonisht, mund të futni gjëra të tilla si:
- 1-1-2024 o 1/1/2024
- 01/01/24 o 01/01/2024
- 1 janar 24 ose 1 janar 2024
- 1 Janar 2024
- 1/1 (nëse e hiqni vitin, Excel e shton atë si parazgjedhje)
Duhet të jesh pak më i rreptë me orarin.Përdorni formatin H:MM ose H:MM:SS. Mund të shtoni "am" ose "pm" nëse keni një format 12-orësh, por zakonisht mjafton edhe formati 24-orësh.
Kini kujdes me vitet me dy shifra: Nëse shkruani "01/01/09", a keni parasysh vitin 2009 apo 1909? Për Excel, vitet më të vogla ose të barabarta me 29 janë në shekullin e 30-të, dhe vitet 99 deri në XNUMX janë në shekullin e XNUMX-të. Për të shmangur konfuzionin, gjithmonë shkruani vitet me katër shifra.
Dallimet midis datës dhe tekstit: burimi i gabimeve
Një nga gabimet më të zakonshme është futja e datave si tekst në vend të datave reale.Nëse shkruani "2024-05-22" dhe qeliza nuk e njeh atë si datë (mban të rreshtuar majtas dhe pa format date), çdo llogaritje do të kthejë gabimin e frikshëm. #VALE! ose sjellje të çuditshme.
Zgjidhja: Përdorni funksionin DATE për të detyruar konvertimin: =DATE(viti; muaji; dita)Nëse i keni pjesët e datës në qeliza të ndryshme (A1: dita, B1: muaji, C1: viti), përdorni =DATA(C1;B1;A1) për t'i kombinuar ato në një datë të vetme të njohur saktë nga Excel.
Për kohët, Excel nuk mund të ruajë kohët pa një datë të lidhur.Nëse shkruani "17:30", Excel e interpreton atë si kohën zero të ditës (0 janar 1900), por prapëseprapë funksionon për llogaritjet e kohës.
Personalizimi i formatit: Si t’i shfaqni datat dhe orët ashtu siç dëshironi
Pasi të dhënat të jenë ruajtur saktë, mund të zgjidhni se si dëshironi që ato të shfaqen.Kjo është çështje formati. Shtypni Ctrl + 1 për të aksesuar formatet e qelizave. Mund të zgjidhni nga formatet e paracaktuara (të shkurtra, të gjata, orë, minutë, sekondë, etj.) ose të krijoni një të personalizuar.
Kodet e zakonshme të formatit për datat dhe orët:
- dd – Ditë me dy shifra (p.sh. 05)
- dddd – Dita e plotë e javës (p.sh. e mërkurë)
- mm – Muaj me dy shifra (p.sh. 07)
- mmmm – Muaj i plotë (p.sh. korrik)
- aa o aaaa – Viti me dy ose katër shifra
- h:mm:ss - Orë, minutë dhe sekondë
- :mm:ss – Orët totale (për ndryshime më të mëdha se 24 orë)
Mashtrim: Nëse doni ta shfaqni muajin si tekst, përdorni TEXT. Për shembull, =TEXT(A1; "mmmm") tregon muajin e plotë.
Funksionet kryesore për trajtimin e datave dhe orëve
Funksionet e datës dhe orës në Excel ju lejojnë të bëni gjithçka, nga llogaritjet e thjeshta deri te automatizimi i detyrave të përsëritura.Disa nga më të dobishmet:
Funksioni DATE
DATA(viti; muaji; dita) Krijo një datë nga tre numra. Kjo është thelbësore nëse po ndani të dhëna ose dëshironi të siguroheni që qeliza njihet si datë.
- Viti: Mund të variojë nga 1900 në 9999. Nëse vendosni vetëm dy shifra, varet nga cilësimet tuaja të Excel-it.
- muaj: Nëse futni më shumë se 12 muaj, Excel i shton muajt në vitin pasardhës. Në mënyrë të ngjashme, nëse futni më pak se 1, zbriten muajt.
- ditë: Nëse tejkaloni numrin e ditëve në muaj, Excel e shton tepricën në muajin pasardhës. Nëse futni një numër negativ, ditët i kthen në muajin paraprak.
Gabime të zakonshme: Nëse përdorni një vit jashtë diapazonit ose negative të pamundura, Excel kthen #NUM!
Funksioni TANI
NOW () o TANI () Ju jep datën dhe orën aktuale. Është dinamike: ndryshon çdo herë që fleta rillogaritet. I dobishëm për vulat kohore automatike, llogaritjet e kohës së kaluar, etj.
Nëse dëshironi vetëm datën aktuale, përdorni SOT () (TODAY () në Anglisht).
Funksioni TEXT
TEXT(vlera; formati_i_tekstit) ju lejon të formatoni një datë ose orë si tekst pikërisht në formatin që dëshironi.
- Shembull: =TEXT(A1; "dd/mm/vvvv") konverton datën në A1 në tekst me atë format.
- Mund të përdorni “mmmm yyyy” për të shfaqur, për shembull, “Korrik 2024”.
- Për të marrë emrin e ditës ose të muajit: "dddd" ose "mmmm".
Funksioni MUAJ
MUAJI (data) nxjerr numrin e muajit nga një datë. Nëse A1 përmban 20/05/2024, =MUAJ(A1) kthen 5.
Gjithashtu mund të shikoni më shumë veçori të dobishme në udhëzuesin tonë mbi formulat thelbësore të Excel-it.
Funksioni WORKDAYS (DITË PUNËS)
DITA E PUNËS(data_e_fillimit; ditë; ) llogarit se cila ditë jave bie pas X ditësh nga një datë, duke injoruar të shtunat, të dielat dhe festat (nëse i keni në një tabelë).
- Shembull: =DITA E PUNËS("01/09/2024"; 10) jep datën 10 ditë pune më vonë.
- Mund të zbritësh ditët duke futur vlera negative.
Funksioni YEARFRAC
YEARFRAC(data_e_fillimit; data_e_mbarimit;) Ju jep pjesën e një viti midis dy datave. Shumë i dobishëm për llogaritjen e interesit, kredive, moshave, etj.
- Baza: përcakton konventën për numërimin e ditëve. Vlera parazgjedhur është 0 (SHBA 30/360), por mund të specifikoni 1 (aktual/aktual), 2 (aktual/360), 3 (aktual/365) ose 4 (Evropian 30/360).
Për shembull, midis 1/1/2023 dhe 30/6/2023, =YEARFRAC(«01/01/2023»; «30/06/2023») jep 0,5 (gjysmë viti).
Kryeni llogaritjet midis datave dhe orëve në Excel
Shtoni/zbritni ditë nga një datë
Ju mund të shtoni ose zbritni çdo numër ditësh në një datë thjesht me mbledhje ose zbritje bazë.: nëse në A1 është 10/04/2024, = A1 + 5 nga 15/04/2024.
Kushtojini vëmendje formatit të qelizës së rezultatit: Nëse shihni një numër të çuditshëm (p.sh. 45000), thjesht formatoni qelizën si datë dhe keni mbaruar.
Llogaritni ndryshimin midis dy datave (në ditë, muaj, vite)
Për të gjetur ditët midis dy datave përdorni "=B1-A1". Nëse dëshironi ndryshimin në muaj ose vite, mund të përdorni funksionet:
- DATEDIF(data_e_fillimit; data_e_mbarimit; “m”) – ndryshimi në muaj
- DATEDIF(data_e_fillimit; data_e_mbarimit; “y”)) – ndryshimi në vite
Shenim: DATEDIF është një funksion "i fshehur"; nuk shfaqet në asistentin e Excel-it, por prapë funksionon.
Shtoni/zbritni kohën në një orë
Shtimi i minutave, orëve ose sekondave është çështje e shtimit të thyesës së duhur.Nëse A1 është “12:00”, për të shtuar 90 minuta: = A1 + 90/1440.
Për të llogaritur se sa kohë ka kaluar midis dy momenteve: =(B1-A1)*24 Ju jep totalin në orë (nëse B1 dhe A1 janë data me orë).
Për të marrë minutat, shumëzojeni me 1440. Nëse rezultatet janë negative, qeliza mund të shfaqë “###”. Ndryshoni formatin në :mm:ss për të parë ndryshimet negative.
Datat dhe orët e kombinuara: punoni me të dyja në të njëjtën kohë
Kur punon me data dhe orë së bashku, Excel e ruan pjesën e plotë si datë dhe pjesën dhjetore si orë.Në këtë mënyrë, mund të kryeni llogaritje të sakta midis pikave specifike në kohë: për shembull, midis "15/04/2024 9:30 AM" dhe "16/04/2024 12:15 PM", diferenca do të jetë 1,11806 (një ditë ose më shumë), të cilën mund ta konvertoni në orë ose minuta duke e shumëzuar thjesht me 24 ose 1440.
Gabimet e zakonshme dhe si t'i shmangni ato
Gabimet gjatë punës me datat dhe orët shkaktojnë më shumë shqetësime sesa çdo lloj tjetër i të dhënave në Excel.. Ja cilat janë më të zakonshmet dhe si t'i zgjidhni ato:
1. Konfuzion në formatin e datës
Shumë gabime vijnë nga keqinterpretimi i datës. (DD/MM/YYYY kundrejt MM/DD/YYYY). Zgjidhja është standardizimi i formateve dhe, nëse është e nevojshme, detyrimi i formatimit me funksionin TEXT.
2. Qeliza e rezultatit tregon ####
Kur qeliza shfaq #####, zakonisht do të thotë që kolona është shumë e ngushtë ose data është negative (e pamundur në Excel). Provo ta zgjerosh kolonën. Nëse gabimi vazhdon, kontrollo formulën tënde.
3. Shfaqet #¡VALUE!
Ndodh kur përpiqeni të veproni me një qelizë ku Excel e konsideron tekstin dhe jo datën/orën.Zgjidhja: Detyroni Excel-in të njohë datën duke përdorur funksionin DATE ose konvertoni qelizat në formatet e duhura.
4. Gabim #NUM!
Ky gabim shfaqet kur përpiqeni të futni data jashtë diapazonit të lejuar (para 1/1/1900 ose pas 31/12/9999)Nëse ju nevojiten data të vjetra (historike), ruajini ato si tekst dhe/ose përdorni truke shtesë.
5. Probleme me vitet e brishta
Vitet e brishta mund të prishin funksionet e ndryshimit të datave, veçanërisht në llogaritjet financiare.Përdorni YEARFRAC me konventën e duhur për të shmangur surprizat. Për të përcaktuar nëse një vit është vit i brishtë: =LEAPYEAR(vit) o =MOD(viti;4)=0.
6. Paqartësia në vitet me dy shifra
Excel ka vendosur një "vit kufitar" për vitet me dy shifra.Kjo mund të shkaktojë gabime historike ose financiare. Është mirë që gjithmonë të futni vite me katër shifra.
7. Dallimet e zonave kohore
Excel nuk i trajton automatikisht ndryshimet e zonës kohoreNëse punoni me zona kohore ndërkombëtare, kompensoni manualisht ndryshimin e kohës. Mënyra më e besueshme për ta bërë këtë është që gjithmonë të konvertoni gjithçka në UTC.
Këshilla praktike për shfaqjen dhe formatimin e datave dhe orëve
Për ta bërë më të lehtë punën tuaj me datat dhe orët në Excel, përfitoni nga këshillat e mëposhtme:
- Shkurtoret e shpejta: Ctrl+SHIFT+# formati si datë; Ctrl+SHIFT+@ si kohë.
- Vendosni datën aktuale: ctrl+;
- Vendos kohën aktuale: Ctrl+Shift+;
- Për të shfaqur vetëm një pjesë të datës: Përdorni formatim të personalizuar (p.sh., “mmmm yyyy” për të shfaqur vetëm muajin dhe vitin në spanjisht).
- Nëse një datë bëhet një numër "i çuditshëm": Formatoni qelizën si datë ose përdorni funksionin TEXT.
- Për të shtuar lehtësisht orë/minuta: Përdor format të personalizuar :mm për të parë orët totale, edhe nëse shuma e tyre është më shumë se 24.
Ndryshimi i sistemit të datës: modaliteti 1900 kundrejt modaliteti 1904
Excel ka dy sisteme për numërimin e ditëve: modaliteti 1900 (i zakonshmi në Windows) dhe modaliteti 1904 (i përdorur në Mac)Nëse kombinoni skedarë që përdorin të dy sistemet, datat mund të jenë të gabuara me 1.462 ditë (rreth katër vjet). Mundësia për të ndryshuar sistemin është te Opsionet > Të avancuara > Gjatë llogaritjes së këtij libri pune > Përdorni sistemin e datave 1904. Unë nuk rekomandoj ta ndryshoni atë nëse nuk është absolutisht e nevojshme.
Puna me datat dhe orët në Excel kërkon kuptimin e bazës së tyre numerike, zgjedhjen e formatit të duhur dhe njohjen e specifikave rajonale dhe kufizimeve të sistemit. Me praktikë dhe vëmendje, do të jeni në gjendje të shmangni gabimet e zakonshme dhe të automatizoni llogaritjet komplekse, duke kursyer kohë dhe duke përmirësuar saktësinë e raporteve tuaja.
