Tushen aiki tare da kwanakin da lokuta a cikin Excel

Video

Kamar yadda ya saba, wanda ke buƙatar sauri - kalli bidiyon. Cikakkun bayanai da nuances - a cikin rubutun da ke ƙasa:

Yadda ake shigar da ranaku da lokuta a cikin Excel

Idan muka tuna da saitunan yanki, to Excel yana ba ku damar shigar da kwanan wata ta hanyoyi daban-daban - kuma ya fahimci su duka:

Bayyanar (nuni) kwanan wata a cikin tantanin halitta na iya bambanta sosai (tare da ko ba tare da shekara ba, wata ɗaya a matsayin lamba ko kalma, da sauransu) kuma an saita ta cikin menu na mahallin - danna-dama akan tantanin halitta sannan sannan Tsarin salula (Tsarin Kwayoyin):

Ana shigar da lokaci cikin sel ta amfani da colons. Misali

16:45

Idan ana so, zaku iya kuma ƙididdige adadin daƙiƙa - shigar da su kuma mai hanji ya rabu:

16:45:30

Kuma, a ƙarshe, babu wanda ya hana ƙayyadadden kwanan wata da lokaci lokaci guda tare ta sararin samaniya, wato 

27.10.2012 16: 45

Saurin shigarwa na kwanakin da lokuta

Don shigar da kwanan wata a cikin tantanin halitta na yanzu, zaku iya amfani da gajeriyar hanyar madannai Ctrl + Ж (ko CTRL+SHIFT+4 idan kuna da harshen tsarin tsoho na daban).

Idan ka kwafi tantanin halitta tare da kwanan wata (jawo daga kusurwar dama ta ƙasan tantanin halitta), riƙe dama maballin linzamin kwamfuta, zaku iya zaɓar yadda ake kwafin kwanan wata da aka zaɓa:

Idan sau da yawa dole ne ku shigar da ranaku daban-daban a cikin sel na takardar, to ya fi dacewa don yin hakan ta amfani da kalandar fashe:

Idan kana son tantanin halitta ya ƙunshi ainihin ranar yau, yana da kyau a yi amfani da aikin TODAY (YAU):

Yadda Excel a zahiri ke adanawa da sarrafa ranaku da lokuta

Idan ka zaɓi tantanin halitta mai kwanan wata kuma saita masa Tsarin gabaɗaya (dama danna kan cell Tsarin salula – tab Number - Janar), kuna iya ganin hoto mai ban sha'awa:

Wato, daga ra'ayi na Excel, 27.10.2012/15/42 41209,65417:XNUMX pm = XNUMX

A zahiri, Excel yana adanawa da aiwatar da kowace rana daidai irin wannan - azaman lamba mai lamba da juzu'i. Matsakaicin juzu'in lamba (41209) shine adadin kwanakin da suka shude tun daga ranar 1 ga Janairu, 1900 (wanda aka ɗauka azaman ma'ana) har zuwa kwanan wata. Da kuma juzu'i (0,65417), bi da bi, rabon ranar (1 day = 1,0)

Daga cikin wadannan hujjoji guda biyu zalla a aikace sun biyo baya:

• Da fari dai, Excel ba zai iya aiki ba (ba tare da ƙarin saiti ba) tare da kwanakin da suka wuce Janairu 1, 1900. Amma za mu tsira daga wannan! 😉
• Na biyu, yana yiwuwa a yi duk wani aikin lissafi tare da kwanan wata da lokuta a cikin Excel. Daidai domin su ne ainihin lambobi! Amma wannan ya riga ya buɗe dama mai yawa ga mai amfani.

Yawan kwanakin tsakanin kwanakin biyu

Ana la'akari da raguwa mai sauƙi - muna cire kwanan watan farko daga ranar ƙarshe kuma mu fassara sakamakon zuwa Janar (Gabaɗaya) Tsarin lamba don nuna bambanci a cikin kwanaki:

Yawan kwanakin kasuwanci tsakanin ranaku biyu

Anan lamarin ya dan kara sarkakiya. Ba za a yi la'akari da Asabar, Lahadi da hutu ba. Don irin wannan lissafin, yana da kyau a yi amfani da aikin MA'AIKATA TSAFTA (KWANAKI NETWORK) daga category Kwanan wata da lokaci. A matsayin muhawara ga wannan aikin, dole ne ka saka kwanan watan farawa da ƙarshen ƙarshe da sel tare da kwanakin karshen mako (ranar hutu na jama'a, kwanakin rashin lafiya, hutu, hutu, da sauransu):

Note: Wannan aikin ya bayyana a daidaitaccen tsarin ayyukan Excel tun daga 2007. A cikin tsofaffin nau'ikan, dole ne ka fara haɗa ƙari Kunshin bincike. Don yin wannan, je zuwa menu Sabis - Ƙara-kan (Kayan aiki - Ƙara-Ins) kuma duba akwatin da yake gaba Kunshin nazari (Analisys Toolpak). Bayan haka, a cikin Wizard Aiki a cikin rukuni Kwanan wata da lokaci aikin da muke bukata zai bayyana MA'AIKATA TSAFTA (KWANAKI NETWORK).

Adadin cikakkun shekaru, watanni, da kwanaki tsakanin kwanakin. Shekaru a cikin shekaru. Kwarewa.

Game da yadda za a lissafta shi daidai, yana da kyau a karanta a nan.

Canja kwanan wata ta ƙayyadadden adadin kwanakin

Tun da ana ɗaukar rana ɗaya a cikin tsarin bayanan kwanan wata na Excel azaman raka'a (duba sama), don ƙididdige kwanan wata wato, a ce, kwanaki 20 daga abin da aka bayar, ya isa ya ƙara wannan lambar zuwa kwanan wata.

Canja kwanan wata ta takamaiman adadin kwanakin kasuwanci

Ana yin wannan aikin ta hanyar aikin RANAR AIKI (RANAR AIKI). Yana ba ku damar ƙididdige kwanan wata da ke gaba ko baya dangane da ranar farawa ta adadin kwanakin aiki da ake so (la'akari da ranar Asabar da Lahadi da kuma hutun jama'a). Amfani da wannan aikin daidai yake da amfani da aikin MA'AIKATA TSAFTA (KWANAKI NETWORK) aka bayyana a sama.

Lissafin ranar mako

Ba ranar litinin aka haife ku ba? Ba? Tabbas? Ana iya bincika shi cikin sauƙi tare da aikin DAY (RANAR MAKO)daga category Kwanan wata da lokaci.

Hujja ta farko na wannan aikin shine tantanin halitta mai kwanan wata, na biyu shine nau'in kirga kwanakin mako (mafi dacewa shine 2).  

Lissafin tazarar lokaci

Tunda lokaci a cikin Excel, kamar yadda aka ambata a sama, lamba ɗaya ne da kwanan wata, amma ɓangaren juzu'in sa ne kawai, to duk wani aiki na lissafin ma yana yiwuwa tare da lokaci, kamar yadda tare da kwanan wata - ƙari, ragi, da sauransu.

Akwai nuance guda ɗaya kawai a nan. Idan, lokacin ƙara tazarar lokaci da yawa, jimlar ta zama fiye da sa'o'i 24, to, Excel zai sake saita shi kuma ya sake farawa daga sifili. Don hana wannan daga faruwa, kuna buƙatar amfani da tsarin zuwa tantanin halitta na ƙarshe 37:30:55:

• Yadda ake lissafin shekaru (kwarewa) a cikin cikakkun shekaru-watanni-kwanaki
• Yadda ake yin faɗuwar kalanda don shigar da kowace rana cikin sauri a kowace tantanin halitta.
• Ƙara kwanan wata ta atomatik zuwa tantanin halitta lokacin shigar da bayanai.
• Yadda ake lissafin ranar Lahadi ta biyu a cikin Fabrairu 2007, da sauransu.