Kwatanta kwanakin a cikin Excel

Sau da yawa, masu amfani da editan rubutu suna buƙatar aiwatar da irin wannan hanya mai wahala kamar kwatanta kwanakin. Ana iya aiwatar da wannan aikin ta hanyoyi daban-daban. A cikin labarin, za mu bincika dalla-dalla duk hanyoyin da ke ba ku damar kwatanta kwanakin a cikin editan maƙunsar rubutu.

Lokacin aiwatarwa a cikin editan falle

Editan maƙunsar bayanai yana ɗaukar lokaci da kwanan wata azaman bayanan lambobi. Shirin yana canza wannan bayanin ta yadda rana ɗaya ta zama daidai da 1. A sakamakon haka, alamar lokaci shine juzu'i na ɗaya. Misali, 12.00 shine 0.5. Editan maƙunsar bayanai yana canza alamomin kwanan wata zuwa ƙimar lamba, wanda yayi daidai da adadin kwanakin daga Janairu 1, 1900 zuwa ƙayyadadden kwanan wata. Alal misali, idan mai amfani ya canza ranar 14.04.1987 / 31881 / 31881, to, zai sami darajar 2. A wasu kalmomi, kwanaki XNUMX sun wuce daga alamar asali. Ana amfani da wannan makanikin yayin ƙididdige ƙimar lokaci. Don ƙididdige adadin kwanakin tsakanin kwanakin XNUMX, ya zama dole a cire ƙaramin lokaci mai nuna alama daga babban alamar lokaci.

Amfani da bayanin DATE a cikin editan tebur

Babban ra'ayi na ma'aikaci yayi kama da haka: DATE (shekara, wata, rana). Ana buƙatar kowace gardama a rubuta a cikin mai aiki. Akwai hanyoyi guda biyu don kafa hujja. Hanya ta farko ta ƙunshi shigar da aka saba na ƙimar lambobi. Hanya ta biyu ta haɗa da shigar da haɗin kai na sel waɗanda ke cikin mahimman bayanan lambobi. Hujja ta farko ita ce darajar lambobi daga 1900 zuwa 9999. Hujja ta biyu ita ce ƙimar lambobi daga 1 zuwa 12. Hujja ta uku ita ce ƙima ta lamba daga 1 zuwa 31.

Misali, idan ka saka darajar lambobi sama da 31 a matsayin ranar, to karin ranar zata koma wata. Idan mai amfani ya shiga kwanaki talatin da biyu a cikin Maris, zai ƙare da farkon Afrilu.

Misalin amfani da afareta yayi kama da haka:

Misali na ƙayyadadden adadin kwanaki a watan Yuni:

Misali yana nuna amfani da haɗin gwiwar salula azaman muhawara:

Amfani da afaretan RAZDAT a cikin editan maƙunsar rubutu

Wannan ma'aikaci yana dawowa tsakanin ƙimar kwanan wata 2. Babban ra'ayi na ma'aikaci yayi kama da haka: RAZDAT(ranar_fara; last_date; code_for_designation_of_count_units). Nau'in lissafin tazara tsakanin alamomin kwanan wata guda biyu:

• "d" - yana nuna alamar ƙarshe a cikin kwanaki;
• "m" - yana nuna jimlar a cikin watanni;
• "y" - yana nuna jimlar a cikin shekaru;
• "ym" - yana nuna jimlar a cikin watanni, ban da shekaru;
• "md" - yana nuna jimlar a cikin kwanaki, ban da shekaru da watanni;
• "yd" - yana nuna jimlar a cikin kwanaki, ban da shekaru.

A wasu nau'ikan editan maƙunsar bayanai, lokacin amfani da matsananciyar gardama 2, mai aiki na iya nuna kuskure. A wannan yanayin, ya fi dacewa don amfani da wasu ƙididdiga.

Misali yana nuna aikin mai aiki:

A cikin editan ma'auni na 2007, wannan ma'aikacin baya cikin tunani, amma har yanzu kuna iya amfani da shi.

Amfani da afaretan YEAR a cikin editan falle

Wannan ma'aikaci yana ba ku damar dawo da shekara a matsayin ƙimar lamba daidai da ƙayyadadden kwanan wata. Ana nuna ƙimar lambobi a cikin kewayo daga 1900 zuwa 9999. Babban nau'i na ma'aikacin YEAR yana da hujja 1. Hujjar kwanan wata lamba ce. Dole ne a rubuta ta ta amfani da ma'aikacin DATE, ko fitar da alamar ƙarshe na lissafin duk wata dabara. Misali yana nuna aikin mai aiki:

Amfani da afaretan MONTH a cikin editan maƙunsar rubutu

Wannan ma'aikaci yana ba ku damar dawo da wata a matsayin ƙimar lamba daidai da ƙayyadadden kwanan wata. Ana nuna ƙimar lamba a cikin kewayon daga 1 zuwa 12. Gabaɗayan nau'in ma'aikacin MONTH yana da hujja 1. Hujja ita ce ranar wata, an rubuta a matsayin ƙima. Dole ne a rubuta ta ta amfani da ma'aikacin DATE, ko fitar da alamar ƙarshe na lissafin duk wata dabara. Yana da kyau a lura cewa wata ɗaya da aka rubuta a cikin fom ɗin rubutu ba za a sarrafa shi daidai da editan maƙunsar rubutu ba. Misali yana nuna aikin mai aiki:

Misalai na amfani da DAY, DAY, DAY, da RANAR MAKO a cikin editan maƙunsar rubutu

Wannan ma'aikaci yana ba ku damar dawo da ranar azaman ƙimar lamba daidai da ƙayyadadden kwanan wata. Ana nuna ƙimar lambobi a cikin kewayon daga 1 zuwa 31. Gabaɗayan nau'in ma'aikacin DAY yana da hujja 1. Hujja ita ce ranar ranar, an rubuta a matsayin ƙimar lambobi. Dole ne a rubuta ta ta amfani da ma'aikacin DATE, ko fitar da alamar ƙarshe na lissafin duk wata dabara. Misali yana nuna aikin mai aiki:

Ma'aikacin, wanda ke da sunan RANAR MAKO, yana ba ku damar mayar da lambar ranar mako na kwanan wata da aka bayar. Ta hanyar tsoho, mai aiki yana ɗaukar Lahadi a matsayin ranar 1st na mako. Misali yana nuna aikin mai aiki:

Ma'aikacin, wanda ke da sunan NOMWEEK, yana ba ku damar nuna lambar ƙa'ida ta mako a cikin kwanan wata da aka bayar. Misali yana nuna aikin mai aiki:

Misali, Mayu 24.05.2015, XNUMX shine mako na ashirin da biyu na shekara. Kamar yadda aka rubuta a sama, shirin ya dauki Lahadi a matsayin ranar farko ta mako.

Hujja ta biyu ita ce 2. Wannan yana bawa editan maƙunsar damar yin la'akari da Litinin a matsayin farkon mako (kawai a cikin wannan dabarar).

Ana amfani da ma'aikacin TODAY don saita kwanan watan. Wannan ma'aikacin ba shi da gardama. Ana amfani da afaretan TDATE() don nuna kwanan wata da lokaci na yanzu.

Ƙarshe da ƙarshe game da kwatanta kwanakin a cikin editan maƙunsar bayanai

Mun gano cewa akwai hanyoyi da masu aiki da yawa don kwatanta kwanakin biyu a cikin editan falle. Zaɓin da ya fi dacewa shine amfani da afaretan RAZNDATA, wanda ke ba ku damar dawo da bambanci tsakanin kwanakin biyu. Bugu da ƙari, kuna iya amfani da dabaru iri ɗaya don dawo da ƙimar rana, wata, da shekara. Kowane mai amfani zai iya zaɓar wa kansa hanya mafi dacewa don kwatanta kwanakin a cikin editan maƙunsar rubutu.