חישוב ההפרש בין שני תאריכים. ההבדל בין תאריכים באקסל מחושב על ידי הפונקציה razndat Difference בין תאריכים בשנים

כדי לחשב את משך מרווחי הזמן, הכי נוח להשתמש בפונקציה הלא מתועדת RAZNDAT( ) , גרסה אנגלית של DATEDIF().

הפונקציה RAZNDAT() אינה בעזרה של EXCEL2007 ובתוכם אשף הפונקציות (מִשׁמֶרֶת+ ו3 ), אבל זה עובד, אם כי לא בלי פגמים.

תחביר פונקציה:

DATE(start_date; end_date; measurement_method)

טַעֲנָה תאריך התחלהחייב לבוא לפני הטיעון תאריך סופי.

טַעֲנָה שיטת מדידהקובע כיצד ובאילו יחידות יימדד המרווח בין תאריכי ההתחלה והסיום. ארגומנט זה יכול לקבל את הערכים הבאים:

להלן תיאור מפורט של כל 6 ערכי הארגומנט שיטת מדידה, כמו גם נוסחאות חלופיות (ניתן להחליף את הפונקציה RAZNDAT() בנוסחאות אחרות (אם כי די מסורבל). זה נעשה בקובץ לדוגמה).

בקובץ לדוגמה, ערך הארגומנט הוא תאריך התחלהמונח בתא A2 , והערך של הטיעון תאריך סופי- בתא ב 2 .

1. הבדל בימים ("ד")

הנוסחה =DATEDAT(A2;B2,"d") תחזיר את ההפרש הפשוט בימים בין שני תאריכים.

דוגמה1:תאריך התחלה 25.02.2007, תאריך סופי 26.02.2007
תוֹצָאָה:יום 1).

דוגמה זו מראה כי בעת חישוב משך השירות יש צורך להשתמש בפונקציה RAZNDAT() בזהירות. ברור שאם עובד עבד ב-25 וב-26 בפברואר, אז הוא עבד 2 ימים, לא 1. כך גם לגבי חישוב חודשים מלאים (ראה להלן).

דוגמה 2:תאריך התחלה 01.02.2007, תאריך סופי 01.03.2007
תוֹצָאָה: 28 (ימים)

דוגמה 3:תאריך התחלה 28.02.2008, תאריך סופי 01.03.2008
תוֹצָאָה: 2 (ימים), כי 2008 היא שנה מעוברת

הערה: אם אתה מעוניין רק בימי עבודה, אזי ניתן לחשב k בין שני תאריכים באמצעות הנוסחה = NETWORKDAYS(B2;A2)

2. הבדל בחודשים מלאים ("מ")

הנוסחה =DATE(A2;B2;"m") תחזיר את מספר החודשים המלאים בין שני תאריכים.

דוגמה1:תאריך התחלה 01.02.2007, תאריך סופי 01.03.2007
תוֹצָאָה: 1 חודש)

דוגמה 2:תאריך התחלה 01.03.2007, תאריך סופי 31.03.2007
תוֹצָאָה: 0

בחישוב משך השירות נחשב שעובד שעבד בכל ימות החודש עבד חודש שלם. הפונקציה RAZNDAT() לא חושבת כך!

דוגמה 3:תאריך התחלה 01.02.2007, תאריך סופי 01.03.2009
תוֹצָאָה: 25 חודשים

=12*(YEAR(B2)-YEAR(A2))-(MONTH(A2)-MONTH(B2))-(DAY(B2)<ДЕНЬ(A2))

תשומת הלב: בעזרה של MS EXCEL (ראה סעיף חישוב גיל) יש נוסחה מעוקלת לחישוב מספר החודשים בין 2 תאריכים:

=(YEAR(TDATE())-YEAR(A3))*12+MONTH(TDATE())-MONTH(A3)

אם במקום הפונקציה TDATE() - התאריך הנוכחי, השתמש בתאריך 31/10/1961, והזן 11/01/1962 ב-A3, אז הנוסחה תחזיר 13, למרות שבפועל עברו 12 חודשים ויום אחד (נובמבר ודצמבר בשנת 1961 + 10 חודשים בשנת 1962).

3. הבדל בשנים שלמות ("y")

הנוסחה =DATE(A2;B2;"y") תחזיר את מספר השנים המלאות בין שני תאריכים.

דוגמה1:תאריך התחלה 01.02.2007, תאריך סופי 01.03.2009
תוֹצָאָה: 2 שנים)

דוגמה 2:תאריך התחלה 01.04.2007, תאריך סופי 01.03.2009
תוֹצָאָה: 1 שנה)

ניתן להחליף את הנוסחה בביטוי חלופי:
=IF(DATE(YEAR(B2),MONTH(A2),DAY(A2))<=B2;
YEAR(B2)-YEAR(A2);YEAR(B2)-YEAR(A2)-1)

4. הפרש חודשים מלאים למעט שנים ("ym")

הנוסחה =DASDAT(A2;B2;"ym") תחזיר את מספר החודשים המלאים בין שני תאריכים, לא כולל שנים (ראה דוגמאות למטה).

דוגמה1:תאריך התחלה 01.02.2007, תאריך סופי 01.03.2009
תוֹצָאָה: 1 (חודש), כי תאריך הסיום 03/01/2009 ותאריך ההתחלה ששונה 02/01 מושוות. 2009 (שנת תאריך ההתחלה מוחלפת בשנה של תאריך הסיום, שכן 01.02 קטן מ-01.03)

דוגמה 2:תאריך התחלה 01.04.2007, תאריך סופי 01.03.2009
תוֹצָאָה: 11 (חודשים), כי תאריך הסיום 03/01/2009 ותאריך ההתחלה ששונה 04/01 מושווים. 2008 (השנה של תאריך ההתחלה מוחלפת בשנה של תאריך הסיום מינוס שנה, כי 01.04 יותר מ-01.03)

ניתן להחליף את הנוסחה בביטוי חלופי:
=REMAT(C7,12)‎
בתא C7 ההפרש חייב להיות בחודשים מלאים (ראה סעיף 2).

5. הבדל בימים לא כולל חודשים ושנים ("md")

הנוסחה =DASDAT(A2;B2;"md") תחזיר את מספר הימים בין שני תאריכים מבלי לקחת בחשבון חודשים ושנים. לא מומלץ להשתמש בפונקציה RAZNDAT() עם ארגומנט זה (ראה דוגמאות למטה).

דוגמה1:תאריך התחלה 01.02.2007, תאריך סופי 06.03.2009
תוצאה 1: 5 (ימים), כי תאריך הסיום 03/06/2009 ותאריך ההתחלה ששונה 01 מושוות. 03 .2009 (השנה והחודש של תאריך ההתחלה מוחלפים בשנה ובחודש של תאריך הסיום, שכן 01 הוא פחות מ-06)

דוגמה 2:תאריך התחלה 28.02.2007, תאריך סופי 28.03.2009
תוצאה 2: 0, כי תאריך הסיום 28/03/2009 ותאריך ההתחלה ששונה 28 מושוות. 03 .2009 (השנה והחודש של תאריך ההתחלה מוחלפים בשנה ובחודש של תאריך הסיום)

דוגמה 3:תאריך התחלה 28.02.2009, תאריך סופי 01.03.2009
תוצאה 3: 4 (ימים) - תוצאה לגמרי לא מובנת ושגויה. התשובה צריכה להיות =1. יתרה מכך, תוצאת החישוב תלויה בגרסת ה-EXCEL.

גרסת EXCEL 2007 עם SP3:

תוצאה - 143 ימים! יותר מימים בחודש!

גרסת EXCEL 2007:

ההבדל בין 28/02/2009 ל-03/01/2009 הוא 4 ימים!

יתרה מכך, ב-EXCEL 2003 עם SP3, הנוסחה מחזירה את התוצאה הנכונה ביום אחד. עבור הערכים 31/12/2009 ו-02/01/2010 התוצאה היא בדרך כלל שלילית (-2 ימים)!

אני לא ממליץ להשתמש בנוסחה עם ערך הארגומנט שלמעלה. ניתן להחליף את הנוסחה בביטוי חלופי:
=IF(DAY(A2)>DAY(B2);
DAY(MONTH(DATEMONTH(B2,-1),0))-DAY(A2)+DAY(B2);
DAY(B2)-DAY(A2))

נוסחה זו היא רק ביטוי שווה ערך (ברוב המקרים) עבור RAZNDAT() עם הפרמטר md. קרא על נכונות הנוסחה הזו בסעיף "שוב על העקמומיות של RAZNDAT()" להלן.

6. הבדל בימים לא כולל שנים ("י"ד")

הנוסחה =DASDAT(A2;B2,"yd") תחזיר את מספר הימים בין שני תאריכים, לא כולל שנים. לא מומלץ להשתמש בו מהסיבות שצוינו בפסקה הקודמת.

התוצאה המוחזרת על ידי הנוסחה =DATEDAT(A2;B2,"yd") תלויה בגרסה של EXCEL.

ניתן להחליף את הנוסחה בביטוי חלופי:
=IF(DATE(YEAR(B2),MONTH(A2),DAY(A2))>B2;
B2-DATE(YEAR(B2)-1,MONTH(A2),DAY(A2));
B2-DATE(YEAR(B2),MONTH(A2),DAY(A2)))

שוב על העקמומיות של RAZNDAT()

בואו למצוא את ההבדל בין התאריכים 16/03/2015 ל-30/01/15. הפונקציה RAZNDAT() עם הפרמטרים md ו-ym תחשב שההפרש הוא חודש ו-14 ימים. באמת?

עם נוסחה שווה ערך ל- RAZNDAT(), אתה יכול להבין את התקדמות החישוב. ברור שבמקרה שלנו מספר החודשים המלאים בין התאריכים = 1, כלומר. כל פברואר. כדי לחשב ימים, הפונקציה מוצאת את מספר הימים בחודש הקודם ביחס לתאריך הסיום, כלומר. 28 (תאריך הסיום שייך למרץ, החודש הקודם הוא פברואר, ובשנת 2015 היו 28 ימים בפברואר). לאחר מכן, הוא מוריד את יום ההתחלה ומוסיף את יום הסיום = DAY(MONTH(DATEMONTH(B6,-1),0))-DAY(A6)+DAY(B6), כלומר 28-30+16=14. לדעתנו, עדיין יש חודש שלם אחד בין התאריכים לכל ימי מרץ, כלומר 16 ימים, לא 14! שגיאה זו מתרחשת כאשר יש פחות ימים בחודש הקודם ביחס לתאריך הסיום מאשר הימים של תאריך ההתחלה. איך לצאת מהמצב הזה?

הבה נשנה את הנוסחה לחישוב ימי ההפרש מבלי לקחת בחשבון חודשים ושנים:

=IF(DAY(A18)>DAY(B18);IF((DAY(MONTH(DATEMONTH(B18,-1),0))-DAY(A18))<0;ДЕНЬ(B18);ДЕНЬ(КОНМЕСЯЦА(ДАТАМЕС(B18;-1);0))-ДЕНЬ(A18)+ДЕНЬ(B18));ДЕНЬ(B18)-ДЕНЬ(A18))

בעת החלת הפונקציה החדשה, יש לקחת בחשבון שההפרש בימים יהיה זהה למספר תאריכי התחלה (ראה איור למעלה, תאריכים 28/01-31/2015). במקרים אחרים, הנוסחאות שוות ערך. באיזו נוסחה עלי להשתמש? זה בידי המשתמש להחליט בהתאם לתנאי המשימה.

הנה קטע קוד:

Query = New Query("SELECT | SELECT | WHENDATE DIFFERENCE(&D1, ADDCDATE(&D2, DAY, 1), DAY) - DATE DIFERENCE(BEGINNING OF PERIOD(&D1, MONTH), BEGINNING OF PERIOD(ADDCDATE(&D2, DAY, 1 ), חודש), יום)< 0 | ТОГДА ДЕНЬ(КОНЕЦПЕРИОДА(&Д1, МЕСЯЦ)) + РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ДЕНЬ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, МЕСЯЦ), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ), ДЕНЬ) | ИНАЧЕ РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ДЕНЬ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, МЕСЯЦ), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ), ДЕНЬ) | КОНЕЦ КАК Дни, | ВЫБОР | КОГДА РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ДЕНЬ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, МЕСЯЦ), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ), ДЕНЬ) < 0 | ТОГДА ВЫБОР | КОГДА РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, ГОД), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД), МЕСЯЦ) < 0 | ТОГДА 12 + РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, ГОД), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД), МЕСЯЦ) | ИНАЧЕ РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, ГОД), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД), МЕСЯЦ) | КОНЕЦ - 1 | ИНАЧЕ ВЫБОР | КОГДА РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, ГОД), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД), МЕСЯЦ) < 0 | ТОГДА 12 + РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, ГОД), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД), МЕСЯЦ) | ИНАЧЕ РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, ГОД), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД), МЕСЯЦ) | КОНЕЦ | КОНЕЦ КАК Месяцы, | ВЫБОР | КОГДА РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, ГОД), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД), МЕСЯЦ) < 0 | ТОГДА РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД) - 1 | ИНАЧЕ РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД) | КОНЕЦ КАК Годы"); Запрос.УстановитьПараметр("Д2",КонечнаяДата); Запрос.УстановитьПараметр("Д1",НачальнаяДата); ТабДМГ=Запрос.Выполнить().Выгрузить(); Стр = ТабДМГ; Сообщить("Разность дат составляет: "+Стр.Дни+" дней "+Стр.Месяцы+" месяцев "+Стр.Годы+" лет ");

כמובן שניתן להוסיף גם איות נכון של ימים, חודשים ושנים בהתאם למספר. אבל זה נושא אחר.

כאן אנו מחשבים:

* לימים- אנו לוקחים את מספר הימים הכולל במרווח ומפחיתים את מספר הימים שכבר נלקח בחשבון, וזהו ההפרש בין תחילת החודשים שלנו מהתאריכים. מספר זה יילקח בחשבון בשורת חישוב החודש. אבל כשמחשבים ימים, אנחנו בודקים את מספר הימים שהתקבלו לשלילה. אם מספר הימים שלילי, זה אומר שעברנו לחודש הבא, אבל למעשה החודש בין התאריכים עדיין לא עבר. כדי לתת את הדעת על כך, נוסיף את מספר הימים בחודש של התאריך הראשון למספר השלילי של הימים.

*במשך חודשים- אנחנו גם לוקחים את סך החודשים ומחסירים את מספר החודשים שכבר נלקח בחשבון, וזהו ההפרש בין התחלות הרגליים שלנו. מספר זה יילקח בחשבון גם בשורת חישוב השנה. אבל כשמחשבים את החודש, בודקים גם את מספר החודשים שהתקבלו בשליליות. אם מספר החודשים שלילי, זה אומר שעברנו לשנה החדשה, אבל למעשה השנה בין התאריכים עדיין לא חלפה. כדי לתת את הדעת על כך, נוסיף 12 למספר השלילי של חודשים.

* לשנה- קח את מספר השנים בין תאריכים. אבל אם מספר החודשים היה שלילי, אז נפחית שנה אחת, כי הוא עדיין לא עבר.

ואני מוסיף יום אחד לדייט השני, כי... אני צריך שהיום האחרון הזה ייכלל בחישוב.

איכשהו ככה. אם יש לך שאלות, תכתוב.

תוכנית משימות נוצרה בגליון עבודה של Excel. עמודה אחת מציגה את תאריך היעד עבור כל משימת סיום. כדי להדגיש את המועדים המאחרים למשימות שהוקצו בצבע, אנחנו צריכים את ההבדל המדויק בין התאריכים באקסל. לשם כך, נשתמש בעיצוב מותנה עם הנוסחה RAZNDAT.

כיצד לחשב את ההפרש בין תאריכים באקסל

יש צורך להדגיש בצבע את שמות המשימות שהמועדים שלהן יפוג בעוד 7 ימים. דוגמה לטבלת תוכנית משימות:

ההשפעה הסופית של הדגשת סוף המועדים לאחר 7 ימים:

כל המשימות שיש להשלים תוך 7 ימים מסומנות בירוק. אם תשנה את הערכים בתא D2, משימות אחרות יודגשו.

עצה מועילה! בתא D2, אתה יכול להשתמש בפונקציה כדי לקבל את התאריך של היום: =TODAY().

נוסחת הפרש תאריכים באקסל

הנוסחה מחזירה את ההבדל בתאריכים בין היום לתאריך היעד בימים. כדי לפתור בעיה זו, השתמש בפונקציה RAZNDAT באקסל: היכן אוכל למצוא את הנוסחה הזו?

לא תמצאו את הפונקציה הזו באשף הפונקציות ואפילו לא בחלונית FORMULAS. יש להזין אותו תמיד באופן ידני. הארגומנט הראשון לפונקציה צריך תמיד להיות התאריך החדש ביותר, והארגומנט השני תמיד צריך להיות התאריך הגבוה ביותר. הארגומנט השלישי של הפונקציה קובע את יחידת המדידה של הכמות שהפונקציה =RAZNDAT() מחזירה. במקרה זה, זהו הסמל "ד" - ימים. המשמעות היא שהפונקציה מחזירה את מספר הימים. לאחר מכן מגיע המפעיל<7. То есть формула проверяет, если функция возвращает число меньше чем 7, то формула возвращает значение ИСТИНА и к текущей ячейке применяется условное форматирование. Ссылки на ячейки в первом аргумент абсолютная (значение неизменяемое), а во втором аргументе – относительная, так как проверятся будут несколько ячеек в столбце C.

במידת הצורך, תוכל להוסיף כלל עיצוב מותנה חדש לטווח זה, שיזהיר אותנו על תום המועד שבועיים מראש. כדי לעשות זאת, עליך להוסיף כלל עיצוב מותנה חדש עבור הטווח A2:A7 ופשוט לשנות מעט את הנוסחה בכלל החדש:

ניתן להגדיר את צבע העיצוב עבור הכלל השני לצהוב. חייבים להחיל 2 כללי עיצוב מותנה על אותו טווח. כדי לבדוק, בחר בכלי: "HOME" - "סגנונות" - "עיצוב מותנה" - "נהל כללים". מכיוון שאנו מבצעים את הכלל העליון תחילה, עלינו לשנות את הסדר שלהם בחלון שמופיע: "מנהל כללי עיצוב מותנה". אחרת, כל המשימות שנבחרו יהיו עם מילוי תאים צהובים. פשוט סמן את הכלל הראשון ולחץ על הכפתור למטה (CTRL+חץ למטה), כפי שמוצג באיור:

כתוצאה מכך, התוכנית מזהירה אותנו תחילה שבועיים, ולאחר מכן שבוע לפני סיום המשימות:

עצה מועילה! אם ישנם כללים רבים שהוקצו לאותו טווח, עקוב אחר היררכיית העדיפות של הסדר שבו הם מבוצעים במנהל ניהול הכללים. ככל שהכלל גבוה יותר, עדיפות הביצוע שלו גבוהה יותר ביחס לאחרים שנמצאים מתחתיו.

כדי לבצע משימות מסוימות באקסל, עליך לקבוע כמה ימים עברו בין תאריכים מסוימים. למרבה המזל, לתוכנית יש כלים שיכולים לפתור בעיה זו. בואו לגלות כיצד ניתן לחשב את הפרש התאריכים באקסל.

לפני שתתחיל לעבוד עם תאריכים, עליך לעצב את התאים כך שיתאימו לפורמט זה. ברוב המקרים, כאשר אתה מזין קבוצה של תווים הדומים לתאריך, התא עצמו עובר פורמט מחדש. אבל עדיף לעשות זאת באופן ידני כדי להגן על עצמך מפני הפתעות.

כעת התוכנית תזהה את כל הנתונים שיהיו כלולים בתאים שנבחרו כתאריך.

שיטה 1: חישוב פשוט

הדרך הקלה ביותר לחשב את ההפרש בימים בין תאריכים היא להשתמש בנוסחה הרגילה.

שיטה 2: פונקציית RAZNDAT

אתה יכול גם להשתמש בפונקציה מיוחדת כדי לחשב את ההבדל בתאריכים RAZNDAT. הבעיה היא שהוא לא נמצא ברשימת אשף הפונקציות, אז תצטרך להזין את הנוסחה באופן ידני. התחביר שלו נראה כך:

RAZNDAT(start_date, end_date, unit)

"יחידה"- זהו הפורמט שבו התוצאה תוצג בתא שנבחר. היחידות שבהן יוחזר הסכום תלוי באיזה תו מוכנס לפרמטר זה:

• "y" - שנים מלאות;
• "מ" - חודשים מלאים;
• "ד" - ימים;
• "ימ" - הפרש חודשים;
• "MD" הוא הפרש הימים (לא נלקחים בחשבון חודשים ושנים);
• "YD" הוא ההבדל בימים (שנים לא נלקחות בחשבון).

כדאי גם לשים לב שבניגוד לשיטת הנוסחה הפשוטה שתוארה לעיל, בעת שימוש בפונקציה זו, תאריך ההתחלה צריך להיות במקום הראשון, ותאריך הסיום במקום השני. אחרת, החישובים יהיו שגויים.

שיטה 3: חישוב מספר ימי העבודה

באקסל אפשר גם לחשב ימי עבודה בין שני תאריכים, כלומר לא כולל סופי שבוע וחגים. לשם כך, השתמש בפונקציה צ'יסטרבני. בניגוד לאופרטור הקודם, הוא קיים ברשימת אשף הפונקציות. התחביר עבור פונקציה זו הוא כדלקמן:

NETWORKDAYS(תאריך_התחלה, תאריך_סיום, [חגים])

בפונקציה זו הארגומנטים העיקריים זהים לאלו של האופרטור RAZNDAT- תאריך התחלה וסיום. יש גם טיעון אופציונלי "חגים".

במקום זאת, עליך להחליף את התאריכים של חופשות שאינן עובדות, אם בכלל, לתקופה המכוסה. הפונקציה מחשבת את כל הימים בטווח שצוין, לא כולל שבתות, ימי ראשון, וכן את אותם ימים שנוספו על ידי המשתמש לארגומנט "חגים".

לאחר המניפולציות לעיל, מספר ימי העבודה עבור התקופה שצוינה יוצג בתא שנבחר מראש.

כפי שאתה יכול לראות, Excel מספק למשתמשים שלו כלים נוחים למדי לחישוב מספר הימים בין שני תאריכים. יחד עם זאת, אם אתה רק צריך לחשב את ההפרש בימים, אז האפשרות הטובה ביותר תהיה להשתמש בנוסחת חיסור פשוטה במקום להשתמש בפונקציה RAZNDAT. אבל אם אתה צריך, למשל, לספור את מספר ימי העבודה, אז הפונקציה תבוא להציל ימי רשת. כלומר, כמו תמיד, המשתמש צריך להחליט על כלי הביצוע לאחר שהגדיר משימה ספציפית.

השתמש בפונקציה DATEDIF כאשר אתה צריך לחשב את ההפרש בין שני תאריכים. ראשית, הזן תאריך התחלה בתא ותאריך סיום בתא אחר. לאחר מכן הזן נוסחה, כגון אחת מהאפשרויות הבאות.

הפרש יום

בדוגמה זו, תאריך ההתחלה נמצא בתא D9 ותאריך הסיום הוא ב-E9. הנוסחה תוצג ב-F9. "ד"מחזירה את מספר הימים המלאים בין שני תאריכים.

הבדל שבוע

בדוגמה זו, תאריך ההתחלה נמצא בתא D13 ותאריך הסיום נמצא ב-E13. "D" מחזיר את מספר הימים. אבל שימו לב מה יש בסוף /7 . זה מחלק את מספר הימים ב-7, מכיוון שיש 7 ימים בשבוע. שימו לב שגם תוצאה זו צריכה להיות בפורמט כמספר. הקש CTRL + 1. לאחר מכן לחץ מספר _gt_ מקומות עשרוניים: 2.

הבדל בחודשים

בדוגמה זו, תאריך ההתחלה נמצא בתא D5 ותאריך הסיום נמצא בתא למטה. בנוסחה "M"מחזירה את מספר החודשים השלמים בין יומיים.

הפרש שנה

בדוגמה זו, תאריך ההתחלה נמצא בתא D2 ותאריך הסיום נמצא ב-E2. "Y"

חישוב גיל בשנים, חודשים וימים שנצברו

1. השתמש ב-RAZNDAT כדי למצוא את מספר השנים הכולל.

בדוגמה זו, תאריך ההתחלה נמצא בתא D17 ותאריך הסיום נמצא ב-E17. בנוסחה "י"מחזירה את מספר השנים השלמות בין יומיים.

2. כדי לחפש חודשים, השתמש שוב ב-RAZNDAT, תוך ציון "GM".

בתא אחר, השתמש בנוסחת RAZNDAT עם הפרמטר "GM". "GM" מחזיר את מספר החודשים שנותרו לאחר השנה המלאה האחרונה.

3. השתמש בנוסחה אחרת כדי למצוא ימים.

כעת עלינו למצוא את מספר הימים שנותרו. ניתן לעשות זאת על ידי כתיבת נוסחה מסוג אחר המוצג לעיל. נוסחה זו מפחיתה את היום הראשון של סוף החודש (05/01/2016) מתאריך הסיום המקורי בתא E17 (05/06/2016). כך זה נעשה: ראשית, הפונקציה DATE יוצרת את התאריך 05/01/2016. הוא נוצר באמצעות השנה בתא E17 והחודש בתא E17. 1 מציין את היום הראשון של החודש. התוצאה של הפונקציה DATE תהיה 05/01/2016. לאחר מכן אנו מפחיתים את התאריך הזה מתאריך הסיום המקורי בתא E17 (05/06/2016), וכתוצאה מכך 5 ימים.

4. אופציונלי: שלב שלוש נוסחאות לאחת.

אתה יכול למקם את כל שלושת החישובים בתא אחד, כפי שמוצג בדוגמה זו. שימוש באמפרסנד, מרכאות וטקסט. זוהי נוסחה ארוכה יותר להזין, אבל לפחות הכל באחד מהם. עֵצָה.הקש Alt+Enter כדי למקם מעברי שורות בנוסחה. זה מקל על הקריאה. כמו כן, אם אינך יכול לראות את הנוסחה כולה, הקש CTRL+SHIFT+U.

הורדת דוגמאות

אתה יכול להוריד ספר לדוגמה עם כל הדוגמאות הניתנות במאמר זה. אתה יכול להירשם אליהם או ליצור נוסחאות משלך.

חישובי תאריך ושעה אחרים

חישוב בין היום למועד אחר

כפי שמוצג לעיל, הפונקציה DATEDIF מחשבת את ההפרש בין תאריך ההתחלה לתאריך הסיום. עם זאת, במקום להזין תאריכים ספציפיים, אתה יכול גם להשתמש בפונקציה היום()בנוסחה. כאשר אתה משתמש בפונקציה TODAY(), Excel משתמש בתאריך הנוכחי במחשב שלך. זכור שאם תפתח את הקובץ שוב בעתיד, הקובץ ישתנה.

חישוב ימי עבודה עם או בלי חגים

השתמש ב-NETWORKDAYS. INTL אם ברצונך לחשב את מספר ימי העסקים בין שני תאריכים. בנוסף, אתה יכול גם לא לכלול סופי שבוע וחגים.

לפני שתתחיל, בצע את השלבים הבאים:החליטו אם לא לכלול את תאריכי החגים. אם כן, הזינו את רשימת תאריכי החגים באזור או בגיליון נפרד. כל יום חג ממוקם בתא נפרד. לאחר מכן בחר את התאים הללו ולאחר מכן בחר נוסחאות _gt_ להקצות שֵׁם. תן שם לטווח micholidaisולחץ על הכפתור בסדר. לאחר מכן צור נוסחה באמצעות השלבים הבאים.

1. הזן את תאריך ההתחלה ותאריך הסיום.

בדוגמה זו, תאריך ההתחלה נמצא בתא D53 ותאריך הסיום נמצא בתא E53.

2. בתא אחר, הזן נוסחה, לדוגמה:

הזן נוסחה, כגון הדוגמה למעלה. 1 בנוסחה מגדיר "שבת" ו"יום ראשון" כימי חופש ומוציא אותם מהסכום הכולל.

הערה. לאקסל 2007 אין NETWORKDAYS. בינלאומי עם זאת, יש לו CLEAR DAYS. הדוגמה לעיל תיראה כך ב-Excel 2007: = NETWORKDAYS (D53, E53). אינך מציין 1 כי NETWORKDAYS מניח שסופי השבוע הם שבת וראשון.

3. שנה ערך 1 במידת הצורך.

אם שבת וראשון אינם חגים, שנה את הערך 1 למשהו אחר ברשימת IntelliSense. לדוגמה, 2 סטים ראשון ושני כסופי שבוע.

אם אתה משתמש ב-Excel 2007, דלג על שלב זה. הפונקציה NETWORKDAYS ב-Excel 2007 תמיד מניחה שסופי שבוע הם שבת וראשון.

4. הזן שם לטווח החגים.

אם יצרת שם טווח חגים בסעיף תחילת העבודה למעלה, הזן אותו בסוף כפי שמוצג להלן. אם אין לך חגים, אתה יכול להשאיר פסיק ו-Micholydays. אם אתה משתמש ב-Excel 2007, הדוגמה לעיל תיראה כך: = NETWORKDAYS (D53, E53, micholidays).

נוֹצָהאם אינך רוצה להתייחס לשם טווח החגים, תוכל גם להזין טווח כמו D35:E:39. לחילופין, ניתן להזין כל חג בנוסחה. לדוגמה, אם החגים הם 1 ו-2 בינואר 2016, הזינו אותם באופן הבא: = ימי רשת. Int (D53, E53, 1, ("1/1/2016", "1/2/2016")). באקסל 2007 זה ייראה כך: = NETWORKDAYS (D53, E53, ("1/1/2016", "1/2 . 2016"})

חישוב הזמן המושקע

כדי לחשב את הזמן המושקע, אתה יכול להחסיר זמן אחד מהשני. ראשית, הזן את שעת ההתחלה בתא ושעת הסיום בתא אחר. ודא שכל הזמנים, כולל שעות, דקות ורווחים, ממולאים לפני הצהריים או אחר הצהריים. הנה מה שאתה צריך לעשות כדי לעשות זאת:

1. הזן את שעת ההתחלה ושעת הסיום.

בדוגמה זו, שעת ההתחלה נמצאת בתא D80 ושעת הסיום היא ב-E80. הקפד להזין שעות, דקות ורווחים לפני התווים AM ו-PM.

2. הגדר את פורמט h/pm.

בחר את שני התאריכים והקש Ctrl+1 (או 1+ ב-Mac). ודא שהאפשרות נבחרת המותאם אישית _gt_ שעות בצהרייםאם הוא עדיין לא מותקן.

3. הפחתת שני ערכים.

בתא אחר, מחסירים את תא ההתחלה מהתא "זמן סיום".

4. הגדר את תבנית השעה.

הקש CTRL+1 (או 1+ ב-Mac). בחר " המותאם אישית _gt_" כדי לא לכלול את התוצאות של "AM" ו-"PM".