آموزش گام به گام طرز کار با فرمول های زمان و تاریخ در اکسل

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

در این مقاله سه ​​مهارت اصلی را به شما آموزش خواهیم داد:

• نحوه قالب بندی تاریخ ها در اکسل به سبک دلخواه شما
• فرمول هایی برای محاسبه تعداد روزها، ماه ها و سال های بین دو تاریخ
• فرمول تاریخ اکسل برای ورود به تاریخ امروز و میانبر صفحه کلید برای اضافه کردن زمان فعلی

Microsoft Excel اساساً می تواند با داده ها هر کاری انجام دهد البته در صورتی که آشنایی لازم را با آن داشته باشید. این مقاله می تواند گام بزرگی در جهت ارتقای مهارت Excel شما باشد. اجازه دهید شروع کنیم.

۱- نحوه تایپ تاریخ

توصیه می کنم تاریخ ها را با همان فرمتی که سیستم شما از آن استفاده می کند تایپ کنید. برای خوانندگان آمریکایی، قالب “سال / ماه / روز” مناسب خواهد بود. قالب اروپایی “سال / روز / ماه” است.

زمانیکه یک تاریخ را تایپ می کنم، همیشه آن را به صورت کامل: یعنی ماه، روز و سال تایپ می کنم. اگر بخواهم تنها ماه و سال را نمایش دهم، به راحتی می تواند آن را قالب بندی کنم (در کمتر از یک دقیقه).

۲- نحوه تایپ زمان

تایپ کردن زمان در اکسل ساده است. می توانیم فقط یک ساعت از روز، یا لحظه دقیق را ثبت کنیم.

اگر می خواهید ساعت ۴ بعد از ظهر را وارد کنید، “۴ pm” را در یک سلول در Excel تایپ کرده و Enter را بزنید:

هنگامی که Enter را فشار می دهیم، اکسل آنچه را که تایپ کرده ایم را به چیزی شبیه این تبدیل می کند: ثانیه:دقیقه:ساعت.

در اینجا نمونه یک زمان خاص در صفحه گسترده را مشاهده می کنید:

نکته اصلی استفاده از Colon ها (دو نقطه) برای جدا کردن بخش های مختلف زمان است، سپس یک جای خالی به اضافه عبارت هایAM یا PM را تایپ می کنیم.

۳- نحوه تایپ تاریخ و زمان با هم

شما می توانید ترکیبی از تاریخ و زمان را در Excel برای زمان های بسیار خاص تایپ کنید.

برای تایپ یک ترکیب تاریخ و زمان، از آنچه قبلاً در مورد تایپ تاریخ و زمان یاد گرفتیم استفاده کنید.

توجه کنید که اکسل به صورت پیش فرض در حالتی که زمان و تاریخ در کنار هم استفاده شوند، زمان را به فرمت ۲۴ ساعته تبدیل می کند. اگر می خواهید سبک این تاریخ را تغییر دهید، ادامه مقاله را مطالعه کنید.

جایزه: میانبر صفحه کلید اکسل برای زمان فعلی

یکی از میانبرهای صفحه کلید مورد علاقه من در اکسل مربوط به وارد کردن زمان فعلی در صفحه گسترده است. من اغلب زمانیکه داده های خود را تغییر می دهم، از این فرمول استفاده می کنم. آن را امتحان کنید:

Control + Shift + ;

قالب بندی تاریخ در اکسل

برای این بخش از مقاله، از برگه “Formatting Dates & Times” در کتاب تمرین نمونه استفاده کنید.

اگر تاریخ های شما به سبک اروپایی باشند چه باید کنید؟ یعنی آنها به صورت سال- ماه- روزهستند و شما می خواهید آنها را به قالب سال- روز- ماه تبدیل کنید.

این سلول ها حاوی اطلاعات یکسانی هستند که به روش های مختلفی قالب بندی شده اند.

در تصویر بالا، آنچه ممکن است شما را غافلگیر کند این است که هر شش سلول شامل اطلاعات یکسانی هستند- “۱/۲۲/۲۰۱۷”. آنچه تفاوت دارد در واقع نحوه قالب بندی آنها در اکسل است. داده های اصلی یکسان هستند، اما می توان برای نمایش آنها از قالب بندی های مختلفی استفاده کرد.

در بیشتر موارد، بهتر است از گزینه formatting  برای اصلاح قالب بندی تاریخ خود استفاده کنید. لازم نیست خود داده ها را اصلاح کنید- فقط کافی است نحوه ارائه آنها را تغییر دهید.

قالب بندی سلول های اکسل

برای تغییر نحوه نمایش اطلاعات تاریخ و زمان، باید از زبانه Home در اکسل استفاده کنید. در ریبون (منوی بالای اکسل)، بخش Number را پیدا کنید.

یک پیکان کوچک در گوشه پایین سمت راست آن بخش وجود دارد. برای باز کردن منوی Format Cells روی آن کلیک کنید.

برای قالب بندی سلول های اکسل که سبک های مختلفی دارند، از برگه Home استفاده کنید و روی پیکان کشویی کنار کلمه “Number” که در تصویر مشاهده می کنید کلیک کنید.

منوی Format Cells گزینه های مختلفی را برای قالب بندی تاریخ و زمان ارائه می کند. شما می توانید با فرمتینگ قالب “۱/۲۲/۲۰۱۷” را به “Sunday, January 22nd”  تبدیل کنید. سپس format painter را بگیرید و قالب تمام سلول های خود را تغییر دهید.

منوی Format Cells به شما این امکان را می دهد که بدون تغییر تاریخ اصلی، سبک های تاریخ و زمان خود را تغییر دهید.

مدتی را صرف بررسی این منو کرده و سبک های مختلف را برای تاریخ ها و زمان های Excel خود امتحان کنید.

دریافت اطلاعات از تاریخ و زمان

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

با فرمول اکسل زیر می توانید سال را از یک تاریخ دریافت کنید:

=YEAR(CELL)

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

=MON(CELL)

پیدا کردن تفاوت بین تاریخ و زمان

برای این قسمت، از برگه “” DATEDIF در کتاب تمرین نمونه استفاده کنید.

در اکسل قالب ها برای تغییر نحوه ارائه تاریخ و زمان استفاده می شوند، در حالیکه فرمول ها برای اصلاح، محاسبه یا کار کردن با تاریخ و زمان به کار می روند.

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

۱- روزهای بین تاریخ ها

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

=DATEDIF(A1,B1,”d”)

فرمول دو سلول را به خود اختصاص می دهد که با کاما از هم جدا می شوند و از حرف  “d” برای محاسبه اختلاف بین روزها استفاده می کند.

فرمول DATEDIF دو سلول تاریخ را به خود اختصاص می دهد و روزهای بین آنها را محاسبه می کند.

در اینجا چند ایده برای استفاده از فرمول تاریخ Excel آورده شده است:

• برای شروع می توانید برای روز تولد خود شمارش معکوس انجام دهید، تفاوت بین امروز و روز تولد خود را محاسبه کنید.
• برای محاسبه تفاوت بین دو تاریخ از DATEDIF استفاده کنید و رشد سهام خود را بر تعداد روزها تقسیم کنید تا رشد (یا ضرر!) روزانه تان محاسبه شود.

۲- ماه های بین تاریخ ها

DATEDIF همچنین تعداد ماه های بین دو تاریخ را نیز محاسبه می کند. این فرمول تاریخ در اکسل بسیار مشابه فرمول قبل است، اما برای محاسبه تفاوت ماه ها، حرفm جایگزین d می شود:

=DATEDIF(A1,B1,”m”)

اگرچه، در نحوه اعمال DATEDIF در اکسل یک تناقض وجود دارد: اکسل ماههای کامل بین دو تاریخ را محاسبه می کند. به تصویر زیر نگاه کنید.

از نظر من، بین اول ژانویه و ۳۱ مارس سه ماه است ( ژانویه، فوریه و تقریباً کل ماه مارس) اما از آنجا که، اکسل ماههای کامل را محاسبه می کند، فقط ژانویه و فوریه را به عنوان ماه کامل، در نظر می گیرد. بنابراین نتیجه “۲” می شود.

در اینجا به روش مورد علاقه من برای محاسبه تعداد ماههای بین دو تاریخ می پردازیم. ابتدا اختلاف تاریخ بین روزها را پیدا می کنیم سپس آن را بر تعداد متوسط ​​روزها در یک ماه تقسیم می کنیم-۴۲٫۳۰٫

=(DATEDIF(A1,B1,”d”)/30.42)

اجازه دهید در اینجا DATEDIF اصلاح شده خود را روی دو تاریخ اعمال کنیم:

خیلی بهتر شد. ۲٫۹۹ به ۳ ماه کامل بسیار نزدیک تر است و این در فرمول های بعدی بسیار مفید خواهد بود.

اسناد رسمی اکسل از روش پیچیده ای برای محاسبه ماه های بین تاریخ ها استفاده می کنند، اما این روش یک روش ساده و آسان برای انجام این کار خواهد بود. نوشتن یک فرمول خوب در اکسل نیازمند دقت و سادگی است و این فرمول هر دو گزینه را دارد.

۳- سال های بین تاریخ ها

در آخر، تعداد سالهای بین دو تاریخ را محاسبه می کنیم. روش رسمی محاسبه سالهای بین دو تاریخ استفاده از فرمول زیر است.

=DATEDIF(A1,B1,”y”)

توجه کنید که این فرمول مشابه فرمول های DATEDIF بالا است، اما در آخرین قسمت فرمول y را جایگزین کرده ایم تا تعداد سال های بین دو تاریخ را محاسبه کند. اجازه دهید آن را به صورت عملی بررسی کنیم:

توجه داشته باشید که این فرمول مانند DATEDIF ماهها عمل می کند: به این معنی که فقط سالهای کامل را در نظر می گیرد. من ترجیح می دهم سالهای غیر کامل را نیز محاسبه کنم. در اینجا یک فرمول DATEDIF بهتر برای سالها آورده شده است:

=(DATEDIF(A1,B2,”d”)/365)

در اصل، ما فقط تفاوت بین دو تاریخ را بر حسب روز بدست می آوریم، سپس آن را تقسیم بر ۳۶۵ می کنیم تا نتیجه بر حسب سال محاسبه شود:

DATEDIF بسیار قدرتمند است، اما مراقب نحوه عملکرد آن باشید: زیرا به صورت پیش فرض تنها ماه ها یا سالهای کامل را، محاسبه می کند. برای بالا بردن دقت در نتایج از نسخه های اصلاح شده من استفاده کنید.

جایزه: روزهای کاری بین تاریخ ها

فرمول های تاریخ اکسل فوق، روی تعداد روزهای بین تاریخ ها تمرکز دارند. اگرچه، گاهی لازم است تعداد روزهای کاری بین دو تاریخ را محاسبه کنیم.

در این حالت، باید از NETWORKDAYS= برای محاسبه تعداد روزهای کاری بین دو تاریخ استفاده شود.

=NETWORKDAYS(A1,B1)

در تصویر زیر، نمونه ای از استفاده از NETWORKDAYS را نشان داده ام. با مشاهده تقویم می توانید نحوه محاسبه فرمول را ببینید.

تاریخ و زمان در صفحه گسترده ها بسیار فراگیر هستند. فرمول های تاریخ اکسل و گزینه های قالب بندی آن مفید هستند. تکنیک های موجود در این مقاله می توانند مهارت های Excel شما را به سطح بالاتری سوق دهند تا بتوانید داده های تاریخ محور را بصورت یکپارچه در صفحات گسترده خود بگنجانید.

• ساده ترین روش ایجاد یک نمودار کلوچه ای در اکسل
• آموزش مدیریت فرمول ها در اکسل (Copy, Paste, Autofill)