پث‌وایز

آموزش اکسل و گوگل شیتدرس ۸ از ۱۲

کار با تاریخ در اکسل: محاسبه‌ی سن، مهلت و روزهای کاری

ببین چرا تاریخ‌ها در اصل عددن؛ بعدش روزهای باقی‌مونده تا موعد تحویل رو بشمار، سن رو حساب کن و روزهای کاری رو با تعطیلات آخر هفته‌ی دلخواهت دربیار.

چیزی که از این درس می‌بری

  • تاریخ زیر پوستش یه عدده؛ پس می‌تونی روزها رو بهش اضافه کنی یا ازش کم کنی.
  • تابع TODAY() هر روز آپدیت می‌شه؛ فرمول =TODAY()-B2 سن یا عمر فاکتور رو بر حسب روز بهت می‌ده.
  • فرمول =DATEDIF(start, end, "Y") تعداد سال‌های کامل رو بهت می‌ده.
فهرست این درس۴
  1. تاریخ در اصل تعداد روزه
  2. سال‌های کامل بین دو تاریخ
  3. شمارش روزهایی که تیمت واقعاً کار می‌کنه
  4. مرور درس

۰۱راز ماجرا

تاریخ در اصل تعداد روزه

اکسل زیر پوستش تاریخ رو به شکل تعداد روز از یه مبدأ مشخص ذخیره می‌کنه و فقط ظاهرش رو شبیه تاریخ نشون می‌ده. برای همینه که تاریخ پایان منهای تاریخ شروع تعداد روزهای بینشون رو بهت می‌ده، و تاریخ + ۳۰ تاریخ حدوداً یه ماه بعد رو بهت می‌ده.

اگه A2 اول مارس و B2 روز ۳۱ مارس باشه، =B2-A2 عدد ۳۰ رو بهت می‌ده.

خودت رو امتحان کنسؤال ۱ از ۴

تاریخ فاکتور توی سلول B2 نوشته شده. کدوم فرمول نشون می‌ده تا امروز چند روز از تاریخ فاکتور گذشته و فردا هم درست کار می‌کنه؟

  1. =B2-TODAY()
  2. =TODAY()-B2
  3. =TODAY()
  4. =B2-30
جواب رو ببین

=TODAY()-B2

درسته. تابع TODAY() هر روز آپدیت می‌شه، و تاریخ جدیدتر منهای تاریخ قدیمی‌تر، یه عدد مثبت برای تعداد روزها بهت می‌ده.

مثال

B2 = تاریخ فاکتور، C2 = سن فاکتور تا امروز (بر حسب روز)

C2:  =TODAY()-B2
D2:  =IF(C2>30, "Overdue", "OK")
E2:  =B2+30          (تاریخ سررسید، با فرمت تاریخ)

خروجی

45
Overdue
تاریخ ۳۰ روز بعد از B2

اگه توی E2 یه عدد بزرگ مثل ۴۶۱۱۲ دیدی، در واقع همون تاریخه که بدون فرمت تاریخ مونده. کافیه یه فرمت تاریخ براش انتخاب کنی تا درست نشون داده بشه.

۰۲محاسبه‌ی سن

سال‌های کامل بین دو تاریخ

تقسیم روزها بر ۳۶۵ به خاطر سال‌های کبیسه خطا می‌ده. فرمول =DATEDIF(start, end, "Y") تعداد سال‌های کامل رو بهت می‌ده؛ آرگومان "M" ماه‌های کامل و "D" روزها رو حساب می‌کنه. این تابع هم توی اکسل و هم گوگل شیت کار می‌کنه، هرچند اکسل موقع تایپ کردن بهت پیشنهادش نمی‌ده.

تاریخ تولد توی B2 باشه: فرمول =DATEDIF(B2, TODAY(), "Y") سن فرد رو حساب می‌کنه.

خودت رو امتحان کنسؤال ۲ از ۴

اگه کسی فردا ۳۰ سالش بشه، فرمول =DATEDIF(B2, C2, "Y") سنش رو ۳۰ حساب می‌کنه.

  1. درست
  2. نادرست
جواب رو ببین

نادرست

غلطه. آرگومان "Y" فقط سال‌های کامل رو می‌شماره، پس تا روز تولدش نرسه هنوز ۲۹ سالشه.

۰۳روزهای کاری

شمارش روزهایی که تیمت واقعاً کار می‌کنه

فرمول =NETWORKDAYS.INTL(start, end, weekend) روزهای بین دو تاریخ رو با حذف تعطیلات آخر هفته می‌شماره. کد آخر هفته مشخص می‌کنه چه روزهایی تعطیلن: کد 1 یعنی شنبه و یکشنبه، 7 یعنی جمعه و شنبه، و 16 یعنی فقط جمعه. می‌تونی یه محدوده از تعطیلات رسمی رو هم به عنوان بخش چهارم اضافه کنی تا اونا هم حساب نشن.

برای تیمی که فقط جمعه‌ها تعطیله: =NETWORKDAYS.INTL(A2, B2, 16, $H$2:$H$20).

خودت رو امتحان کنسؤال ۳ از ۴

هر سؤال رو به فرمولش وصل کن

  • روزهای باقی‌مونده تا موعد تحویل توی B2
  • سن فرد از روی تاریخ تولد توی B2
  • روزهای کاری با تعطیلی فقط جمعه
  • تاریخ ۱۴ روز بعد از B2

=B2+14=B2-TODAY()=DATEDIF(B2, TODAY(), "Y")=NETWORKDAYS.INTL(A2, B2, 16)

جواب رو ببین
  • روزهای باقی‌مونده تا موعد تحویل توی B2 → =B2-TODAY()
  • سن فرد از روی تاریخ تولد توی B2 → =DATEDIF(B2, TODAY(), "Y")
  • روزهای کاری با تعطیلی فقط جمعه → =NETWORKDAYS.INTL(A2, B2, 16)
  • تاریخ ۱۴ روز بعد از B2 → =B2+14

خودت رو امتحان کنسؤال ۴ از ۴

یه پروژه از A2 تا B2 طول می‌کشه و شرکت پنج‌شنبه‌ها و جمعه‌ها تعطیله. الهام فرمول =NETWORKDAYS(A2, B2) رو می‌نویسه. ایراد کار چیه؟

  1. هیچ ایرادی نداره، درسته
  2. تابع NETWORKDAYS تعطیلی رو شنبه و یکشنبه فرض می‌کنه؛ باید از NETWORKDAYS.INTL با کد آخر هفته‌ی مناسب استفاده کنه
  3. به جاش باید تاریخ‌ها رو از هم کم کنه
  4. تابع NETWORKDAYS فقط توی یه ماه کار می‌کنه
جواب رو ببین

تابع NETWORKDAYS تعطیلی رو شنبه و یکشنبه فرض می‌کنه؛ باید از NETWORKDAYS.INTL با کد آخر هفته‌ی مناسب استفاده کنه

درسته. تابع معمولی NETWORKDAYS همیشه شنبه و یکشنبه رو تعطیل در نظر می‌گیره. ولی NETWORKDAYS.INTL با کد آخر هفته (کد 6 یعنی پنج‌شنبه و جمعه) با روزهای کاری شرکت الهام هماهنگه.

۰۴جمع‌بندی

مرور درس

  • تاریخ زیر پوستش یه عدده؛ پس می‌تونی روزها رو بهش اضافه کنی یا ازش کم کنی.
  • تابع TODAY() هر روز آپدیت می‌شه؛ فرمول =TODAY()-B2 سن یا عمر فاکتور رو بر حسب روز بهت می‌ده.
  • فرمول =DATEDIF(start, end, "Y") تعداد سال‌های کامل رو بهت می‌ده.
  • تابع NETWORKDAYS.INTL روزهای کاری رو با کد آخر هفته‌ی دلخواهت و تعطیلات رسمی می‌شماره.

به سبک خودت یادش بگیر.

توی اپ از یادآی بخواه این درس رو ساده‌تر توضیح بده، از زندگی خودت مثال بزنه یا عمیق‌تر بره. بعد یه مسیر کامل دور هدف خودت بساز.

Cafe Bazaar Myket توی اپ شخصی‌سازیش کن