Kashe Kuskuren Kwanan Wata a cikin Formulas ta yin amfani da DATE Function
Dates da DATE Function Overview
Ayyukan DATE na Rubutun Google zai dawo da kwanan wata ko lambar jeri na kwanan wata ta haɗu da kowace rana, wata da kuma shekara abubuwa da aka shigar a matsayin muhawarar aikin .
Alal misali, idan aikin DATE na gaba ya shigar dashi a cikin wani sashin aiki,
= DATE (2016,01,16)
an dawo lambar serial 42385 , wanda ke nufin ranar Janairu 16, 2016.
Canza Lissafin Lissafi zuwa Yanayi
Lokacin da ya shiga kan kansa - kamar yadda aka nuna a cikin dakin D4 a cikin hoton da ke sama, ana amfani da lambar saitin don nuna ranar. Matakan da ake buƙatar cim ma wannan aikin an lissafta su a kasa idan an buƙata.
Shiga Dates a matsayin Dates
Idan aka hade tare da sauran ayyukan Rubutun Google, DATE za a iya amfani dasu don samar da nau'in samfurin zamani kamar yadda aka nuna a hoton da ke sama.
Ɗaya daga cikin muhimmancin amfani don aikin - kamar yadda aka nuna a cikin layuka 5 zuwa 10 a cikin hoton da ke sama - shine tabbatar da cewa an shigar da kwanakin kuma an fassara su da kyau ta hanyar wasu ayyukan kwanan wata na Google. Wannan gaskiya ne idan an tsara bayanan da aka tsara a matsayin rubutu.
An fara amfani da DATE aiki:
- don nuna kwanan wata da ya haɗu kwanakin abubuwa - kamar shekara, wata, ko rana daga wurare daban-daban a cikin takardun aiki
- don tabbatar da cewa kwanakin da aka yi amfani da su a lissafin ana fassara su a matsayin kwanakin (lambar lambobi) maimakon bayanan rubutu
Ayyukan DATE & # 39; s Syntax da Arguments
Haɗin aikin yana nufin layout na aikin kuma ya haɗa da sunan aikin, shafuka, da muhawara.
Haɗin aikin DATE shine:
= DATE (shekara, wata, rana)
shekara - (buƙatar) shigar da shekara a matsayin lambobi huɗu (yyyy) ko tantancewar salula zuwa wurinta a cikin takardar aiki
watan - (da ake buƙatar) shigar da wata a matsayin lambar lambobi biyu (mm) ko tantancewar salula zuwa wurinta a cikin takardar aiki
rana - (da ake buƙata) shigar da ranar a matsayin lambar lambobi biyu (dd) ko tantancewar salula zuwa wurinta a cikin takardar aiki
- da #VALUE! An mayar da darajar kuskure ta wurin aikin idan an shigar da bayanai na rubutu maimakon nau'i - wannan ya haɗa da shigar da ƙira zuwa cell dauke da rubutu
- #NUM! An dawo da darajar kuskure ta wurin aikin idan an shigar da lambobi biyar - irin su 20016 - don jayayya ta shekara
- idan wani tasiri mai banƙyama ga watanni ko jayayya na yau, aikin yana daidaita tsarin fitar da aikin zuwa kwanan wata mai zuwa. Misali,
- = DATE (2016,13,1) - wanda ke da 13 ga hujjar wata - daidaita batun jigidar shekara kuma ya dawo 1/1/2017
- = DATE (2016,01,32) - wanda yake da kwanaki 32 ga watan Janairu - ya daidaita batun jigilar watanni kuma ya dawo 2/01/2016
- idan an shigar da dabi'un adadi don jayayya, ana darajar tamanin zuwa darajar mahaɗan. Misali, darajar "10.25" za a fassara ta "10" ta wurin aikin
Misali DATE Aiki
A cikin hoton da ke sama, ana amfani da aikin DATE tare da wasu ayyuka masu yawa a cikin adadin kwanan wata.
An tsara siffofin da aka lissafa a matsayin samfurin na amfani da DATE. Dabarar a cikin:
- jere na 5 ya shiga ranar farko ta wannan watan;
- jere na 6 ya juya sautin rubutu (cellular A5) cikin kwanan wata;
- jere na 7 yana nuna rana ta mako don kwanan wata;
- jere na 8 lambobi tsakanin kwanakin baya da kwanakin baya;
- jere na 9 ya juyo lambar Julian Day (cell A9) zuwa kwanan wata;
- jere na 10 ya juyar da kwanan wata (cell A10) zuwa zuwa ranar Julian Day.
Bayanin da ke ƙasa ya rufe matakan da ake amfani dashi don shigar da aikin DATE wanda yake cikin sel B4. Sakamakon aikin a cikin wannan yanayin ya nuna lokacin kwanan wata da aka ƙera ta hada haɗin mutum wanda ke cikin sel A2 zuwa C2.
Shigar da aikin DATE
Zaɓuɓɓuka don shigar da aikin da ƙididdigarsa a cikin takardun aiki sun haɗa da:
1) Yin amfani da hannu a cikin cikakken aiki - kawai ka tuna cewa tsari dole ne yyyy, mm, dd irin su:
= DATE (2016,01,16) ko,
= DATE (A2, B2, C2) idan ana yin amfani da bayanan salula
2) Yin amfani da akwatin zane - zane don shigar da aikin da jayayya
Shafukan yanar gizo na Google bazai amfani da akwatunan maganganu don shigar da muhawarar aiki kamar yadda ake samu a Excel. Maimakon haka, yana da akwati na nuna kai tsaye wanda ya tashi kamar yadda aikin aikin ya shiga cikin tantanin halitta.
Ƙungiyoyi masu rarraba
Lokacin yin amfani da ko wane hanya don shigar da aikin, lura cewa ana amfani da na'urori ( , ) don rarraba muhawarar aikin a cikin zangon zagaye.
Matakan da ke ƙasa suna rufe yadda za a shigar da aikin DATE wanda yake cikin sel B4 a cikin hoton da ke sama ta amfani da akwatin da aka bada shawara .
- Danna kan tantanin halitta D4 don sa shi tantanin halitta mai aiki - wannan shi ne inda za a nuna sakamakon DATE
- Rubuta alamar daidai (=) biye da sunan aikin - kwanan wata
- Yayin da kake bugawa, akwatin zane - zane yana nuna tare da sunaye da haɗin ayyukan da suka fara tare da harafin D
- Lokacin da DATE ya bayyana a cikin akwati, danna sunan tare da maɓallin linzamin kwamfuta don shigar da sunan aikin kuma buɗe sashin layi a cikin D4 cell
- Danna kan salula A2 a cikin takardar aiki don shigar da wannan tantanin halitta kamar yadda ya dace a shekara
- Bayan nazarin tantanin halitta, rubuta takaddama ( , ) don aiki a matsayin mai raba tsakanin gardama
- Latsa sel B2 don shigar da wannan tantanin halitta kamar wata hujja ta wata
- Bayan tantanin salula, rubuta wani takaddama
- Danna kan C2 C2 don shigar da wannan tantanin halitta kamar shaida ta day
- Latsa maɓallin shigarwa a kan keyboard don shigar da takalmin rufewa " ) " kuma don kammala aikin
- Dole ne kwanan wata ya bayyana a cell B1 a cikin tsarin 11/15/2015
- Lokacin da ka danna kan tantanin halitta B1 cikakken aikin = DATE (A2, B2, C2) ya bayyana a cikin maɓallin tsari a sama da takardun aiki
Lura : idan fitarwa a cikin tantanin halitta B4 ba daidai ba ne bayan shigar da aikin, yana yiwuwa kwayar halitta an tsara ta ba daidai ba. Da ke ƙasa an jera matakai don canja tsarin kwanan wata.
Canza Kwanan wata Tsarin
Don canza zuwa tsarin kwanan wata a cikin Shafukan Gidan Google
- Gano sel a cikin takardun aiki wanda ya ƙunshi ko zai ƙunshi kwanakin
- Danna Tsarin> Lamba> Kwanan wata a cikin menu don canza tsarin tsarawa zuwa tsarin kwanan wata da saitunan yankuna na yanzu suke amfani - duba ƙasa don canja saitunan yanki.
Canza Saitunan Yanki
Kamar yawancin layi na kan layi, Fassara na Google sun ba da ladabi ga tsarin kwanan nan na Amurka - wanda aka sani da shi tsakanin tsakiya / MM / DD / YYYY.
Idan wurinka yana amfani da tsarin kwanan wata daban - irin su babban-endian (YYYY / MM / DD) ko ɗan-karshen (DD / MM / YYYY) Ana iya gyara fayilolin Google ɗin don nuna ranar a daidaiccen tsari ta daidaita daidaitattun yankuna .
Don canja saitunan yanki:
- Click File don buɗe menu na Fayil;
- Danna kan saitunan Lissafi na ... don buɗe akwatin maganganun Saituna ;
- A ƙarƙashin Siffar a cikin akwatin maganganu, danna kan akwatin - Ƙimar na Amurka - don ganin jerin samfurori na ƙasashe;
- Danna kan ƙasarka ta zaɓa don yin shi zaɓi na yanzu;
- Danna Ajiyayyen saituna a kasan akwatin maganganu don rufe shi kuma komawa zuwa takardun aiki;
- Sabuwar kwanakin da aka shiga cikin takarda aiki ya bi tsarin da aka zaɓa - kwanakin da aka zaɓa na iya buƙatar sake tsara su don canji don ɗaukar tasiri.
Lissafin Jirgin Kasa da Yanayi Dates
By tsoho, Microsoft Excel na Windows yana amfani da tsarin kwanan wata wanda zai fara a shekara ta 1900. Shigar da lambar serial na 0 ya dawo ranar: Janairu 0, 1900. Bugu da ƙari, aikin DATE na Excel ba zai nuna kwanakin kafin 1900 ba.
Shafukan Lissafi na Google suna amfani da ranar Disamba 30, 1899 don lambar zeren, amma ba kamar Excel ba, Shafukan Rubutun Google suna nuna kwanakin kafin wannan ta amfani da lambobin ƙananan don lambar serial.
Alal misali, ranar Janairu 1, 1800 tana haifar da lambar serial -36522 a cikin Shafukan Rubutun Google sannan ya ba da damar yin amfani da shi a cikin matakan, irin su rantsar da Janairu 1, 1850 - Janairu 1, 1800 wanda ya kawo darajar 18, 262 - yawan kwanakin tsakanin kwanakin biyu.
Lokacin da aka shigar da wannan kwanan wata a cikin Excel, a gefe guda, shirin ya canza kwanan wata zuwa bayanan rubutu kuma ya sake dawo da #VALUE! kuskuren kuskure idan an yi amfani da kwanan wata a wata hanya.
Lambar Julian
Littafin Julian Day, kamar yadda wasu hukumomi na gwamnati da wasu kungiyoyi suke amfani da su, lambobi ne na wakiltar wani shekara da rana. Tsawon waɗannan lambobin ya bambanta dangane da yawancin lambobin da ake amfani da su don wakiltar shekara da rana na ɓangaren lamba.
Alal misali, a cikin hoton da ke sama, ranar Julian Day Number a cikin salula A9 - 2016007 - yana da lambobi bakwai tare da lambobin farko na huɗu na lambar suna wakiltar shekara da uku na ƙarshe a ranar. Kamar yadda aka nuna a cikin bita B9, wannan lambar tana wakiltar ranar bakwai ta shekarar 2016 ko Janairu 7, 2016.
Hakazalika, lambar 2010345 ta wakiltar ranar 345th na shekarar 2010 ko 11 ga Disamba, 2010.