توابع تاریخ و زمان در اکسل (آموزش تضمینی و رایگان +فیلم آموزش)|آکادمي استاد آموز

نکته : قسمت صحیح این عدد سریالی بیانگر تاریخ و قسمت اعشاری عدد بیانگر ساعت ، دقیقه و ثانیه  می باشد .

توابع اکسل با مثال :

یعنی اگر ساعت در خانه های اکسل وارد کنیم و فرمت آن را به General تغییر دهیم عدد اعشاری می بینیم. برای مثال با ورود ساعت 12:35:12  و تغییر فرمت  خانه به General ، عدد اعشاری   0.524444444 را می بینیم.

و اگر در خانه ای تاریخ وارد کنیم برای مثال 2019/11/10 با تغییر فرمت سلول به General ، عدد 43779 را مشاهده می کنیم .

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

محاسبه یک ساعت در اکسل

همان طور که بیان شد عدد 1 در اکسل به مفهوم  1 روز است  و اگر یک روز را 24 ساعت در نظر بگیریم  پس یک ساعت می شود یک تقسیم بر 24

0.0417≅0.041667=1/24=محاسبه یک ساعت

پس اگر در اکسل بخواهیم بدانیم مثلا 3 ساعت چه عددی می شود باید 3 را در عدد بالا ضرب کنیم و بنویسیم  .

0.1251=3*0.0417

اگر عدد 0.1251 را بخواهیم به فرمت ساعت ببینیم کافی است گزینه Time را از number format انتخاب کنیم . تاعدد فوق را به فرمت بتوانیم  03:00:00  را ببینیم .

 محاسبه یک دقیقه در اکسل

یک ساعت معادل 60 دقیقه می باشد پس اگر یک ساعت که معادل 0.0417 است را تقسیم بر 60 کنیم یک دقیقه در اکسل بدست می آید

0.000694=0.0417/60=محاسبه یک دقیقه

پس برای محاسبه 7 دقیقه در اکسل عدد بالا را در 7 ضرب می کنیم 0.0048611 نمایش داده می شود و با تغییر فرمت سلول به Time می توانیم فرمت 00:07:0  را مشاهد کنیم .

0.0048611=7*0.000694

محاسبه یک روز در اکسل

همانطور که در قسمت بالا بیان شد عدد یک در اکسل به معنی 1 روز است یعنی اگر در خانه ای عدد 1 را بنویسیم و فرمت آن خانه را به فرمت Date  طبق  روشی  که در بالا توضیح داده شد ، تغییر دهیم  تاریخ مبدا اکسل (1 ژانویه 1900) را نمایش می دهد اگر عدد 5 را وارد کنیم و فرمت سلول را به date تغییر دهیم 5 ژانویه 1900 را نشان می دهد .

دقت کنید که تاریخ مبدا اکسل 1/1/1900 است و اگر تاریخی که از این تاریخ کوچکتر است در خانه های اکسل وارد کنیم باز همان تاریخ 1/1/1900 نمایش داده می شود .

برای مثال در خانه ای( 1/1/1850 = )را می نویسیم بعد از فشردن دکمه Enter از کی بورد تاریخ 1/1/1900 نشان داده می شود .

گفتیم اگر عدد 5 را  در سلولی وارد کنیم و فرمت آنرا به Date تغییر دهیم 5 ژانویه 1900 (1/5/1900) را نشان می دهد  اگر عدد 5 را با یک ساعت اکسل که معادل 0.0417 جمع کنیم حاصل می شود 5.0417  عدد صحیح 5 معرف تاریخ می باشد و عدد اعشاری معرف ساعت و دقیقه و ثانیه است .

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

 توابع اکسل با مثال :(آموزش توابع اکسل پیشرفته )

برای استفاده از توابع تاریخ در اکسل ابتدا تاریخی را در خانه اکسل وارد می کنیم . برای نمایش تاریخ فعلی در خانه های اکسل ، خانه ای را به دلخواه انتخاب می کنیم و همزمان دو دکمه Ctrl و ; را با هم فشار می دهیم تاریخ فعلی سیستم نشان داده می شود و یا اینکه می توانیم از تابع  ()Today که در قسمت های بعدی توضیح خواهم داد ، استفاده کنیم  .

1. تابع ()year

2. تابع ()Mounth

3. تابع ()day

4. تابع()Days

5. تابع()Networkdays

6. تابع()Weekday

7. تابع()Today

8. تابع()Now

9. تابع ()Date

10. تابع()EDate

11. تابعEomonth

12. تابع()Datevalue

13. تابع()Weeknum

تابع()Year:

• این تابع تاریخ و یا سریال عددی مربوط به تاریخ را به عنوان ورودی دریافت می کند و فقط عدد سال آن را به عنوان خروجی بر می گرداند .
• خروجی این تابع مقدار سال است که عددی بین 1900 تا 9999 است .
• در صورت ورود تاریخی که مقدار سال آن بین  مقادیر فوق نباشد تابع ارور value# می دهد .

ساختار تابع()Year

year(serial_number)=

2019=Year(2019/5/12)

بجای تاریخ فوق می توان عدد سریالی تاریخ که عدد 43597 می باشد را به عنوان ورودی وارد کنیم. برای بدست آوردن عدد سریالی ، تاریخ را در خانه های اکسل می نویسیم از تب Home گزینه Number Format  را انتخاب و از منوی باز شده روی گزینه General  کلیک می کنیم.

2019=Year(43597)

تابع()Month:

• این تابع تاریخ و یا عدد سریالی  را به عنوان ورودی دریافت می کند و فقط عدد ماه (1 تا 12) آن را به عنوان خروجی بر می گرداند .
• در صورت ورود تاریخ که مقدار ماه به غیر از مقادیر فوق (1 تا 12) باشد تابع ارور value# می دهد .

ساختار تابع()Month

Month(serial_number)=

11=month(1900/11/5)=

تابع()Day:

• این تابع تاریخ و یا عدد سریالی را به عنوان ورودی دریافت می کند و فقط عدد روز (1 تا 31) آن را به عنوان خروجی بر می گرداند .
• در صورت ورود تاریخ که مقدار روز آن  غیر از مقادیر فوق باشد تابع ارور value# می دهد .
  

ساختار تابع()Day

Day(serial_number)=

1=Day(2019/5/1)=

تابع()Days

این تابع تعداد روزهای بین دو تاریخ پایان و شروع  را می شمارد و بصورت عدد صحیح بر می گرداند.

اگر تاریخ پایان بزرگتر از تاریخ شروع باشد عدد صحیح مثبت و در غیر این صورت منفی می باشد

ساختار تابع Days

Days(End_date;Start_date)=

5=days(2015;12;10;2015/12/05)=

مقادیر Start_date و End_date می تواند متنی باشد لذا در مثال ذیل تاریخ ها بین ” ” قرار گرفته اند .

5=days(“2015;12;10“;“2015/12/05“)=

بجای تاریخ شروع و پایان می توانیم از آدرس خانه ها استفاده کنیم.

days(B3;B2)=

بجای تاریخ شروع(2015/12/05) و پایان بالا (2015/12/10)می توان از عدد سریالی آنها استفاده کرد.برای این کار آدرس دو خانه مربوط به تاریخ شروع و پایان را انتخاب از تب Home   گروه Number  از گزینه Number Format ، روی General کلیک می کنیم .

5=Days(42348;42343)=

تابع()Networkdays

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

ساختار تابع Networkdays 

networkdays(start_date;End_date)=

درج تاریخ در اکسل(فرمول تاریخ روز در اکسل)

برای درج تاریخ در اکسل می توانیم از تابع() Todayبرای درج تاریخ فعلی سیستم در اکسل استفاده می شود  .

ساختار تابع()Today

()Today=

حل مشکل تاریخ شمسی در اکسل

روش اول حل مشکل تاریخ در اکسل :

برای درج تاریخ شمسی در اکسل همانطور که عرض کردم از تابع() Today استفاده می کنیم اما اگر بخواهیم تاریخ به فرمت شمسی نمایش داده شود نه میلادی ، باید سلول مورد نظر را انتخاب کنیم و وارد پنجره فرمت سل شویم و از تب number  گزینه date را انتخاب سپس  گزینه locate  و گزینه ی Calendar type را روی persian قرار می دهیم . وحالا اگر در خانه تاریخ وارد کنیم به فرمت تاریخ شمسی نماش داده می شود .

اگر بعد از این کار بازهم تاریخ میلادی بود از تب Home گروه Alignment گزینه Context یا right to left را انتخاب نمایید .

برای تعیین اینکه در سلول تاریخ ، اول ماه یا روز یا سال ، در تاریخ شمسی نمایش داده شود باید وارد پنجره تنظیمات کنترل پنل شویم سپس گزینه Region را انتخاب می کنیم سپس Additional setting  را انتخاب می کنیم و در تب Data گزینه Short date را بصورت yyyy/mm/dd وارد می کنیم.

y yyy: به معنی سال هست و mm: نمایانگر ماه  dd: به معنی روز در تاریخ شمسی می باشد .

روش دوم حل مشکل تاریخ در اکسل :

سلول های مورد نظر را انتخاب می کنیم وارد پنجره فرمت سل می شویم(Ctrl+1)  و در تب Number گزینه Custome را انتخاب می کنیم . سپس [$-fa-IR,16]dd/mm/yyyy;@ را از لیست انتخاب می کنیم تا فرمت سلول ها به فارسی نمایش داده شود .

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

روش اول :برای افزودن روز به تاریخ در اکسل ، مراحل بالا را تکرار می کنیم و در پنجره فرمت سل تب number گزینه date را انتخاب می کنیم و در پنجره بازشده گزینه type یعنی نوع نمایش تاریخ ،طبق شکل بالا مدلی انتخاب می کنیم (برای مثال :چهارشنبه 24 اسفند 1390)که روز هم در آن باشد .

درج اتوماتیک تاریخ در اکسل

روش اول درج اتوماتیک تاریخ در اکسل :

اگر بخواهیم تاریخ های متوالی درخانه های اکسل وارد کنیم از ابزار Autofill  باید استفاده کنیم .تاریخ را دریک سلول وارد می کنیم و اینتر را می فشاریم سپس خانه ای را که تاریخ را در آن نوشتیم را انتخاب و سمت راست یا چپ پایین سلول نقطه ای مشاهده می شود (مانند شکل بالا که با دایره قرمز نمایش داده شده است)  روی نقطه ماوس را نگه میداریم شکل ماوس که عوض شد به سمت پایین درگ می کنیم.

با این کار بصورت اتوماتیک (fillseries)در بقیه خانه ها تاریخ درج می شود . 

ثبت خودکار ساعت در اکسل:

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

روش دوم درج اتوماتیک تاریخ در اکسل:

 در خانه ای تاریخ را وارد می کنیم و دکمه اینتر را می فشاریم سپس خانه اول و بقیه خانه ها که فرار است تاریخ متوالی در آن بصورت اتوماتیک درج شود را انتخاب می کنیم و از تب home ، گروه Editing  گزینه fill و سپس Fillseries را انتخاب می کنیم . تاریخ در خانه های انتخابی اکسل بطور اتوماتیک درج می شود .

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

جمع و تفریق تاریخ شمسی در اکسل

جمع عدد با تاریخ شمسی در اکسل

اگر در سلول اکسل تاریخی را به فرمت میلادی وارد کنیم برای مثال با نوشتن تابع ()today تاریخ امروز (11/1/2024)درج می شود و اگر در سلول دیگر این تاریخ را با عددی مثل 5  جمع کنیم جواب صحیح (16/1/2024)می دهد .

اما اگر تاریخ را بصورت شمسی وارد کنیم سپس تاریخ را با عددی جمع یا تفریق کنیم خطای value#می دهد .

برای حل این مشکل تاریخ میلادی وارد می کنیم سپس با روش هایی که در قسمت بالا توضیح دادم در این سلول و سلول هایی  که می خواهیم محاسبات تاریخ شمسی انجام دهیم  فرمت نمایش تاریخ را شمسی(از تب فرمت سل سپس تب number و گزینه data را انتخاب می کنیم و تاریخ را شمسی می کنیم ) می کنیم .

حالا اگر محاسبات عددی روی تاریخ انجام دهیم مقدار صحیح نمایش می دهد و می توانیم جمع و تفریق تاریخ شمسی را اکسل انجام دهیم.(تفریق تاریخ در اکسل)

تابع()Now:

این تابع ،تاریخ و ساعت فعلی سیستم را با هم برمی گرداند و نشان می دهد .

ساختار تابع()Now

()Now=

برای مثال : اگر سال تاریخ فعلی را بخواهیم بدست آوریم همانطور که قبلا بیان شد با استفاده از تابع ()Now تاریخ فعلی سیستم را بدست می آوریم حال اگر تابع Now ورودی تابع Year شود  سال تاریخ فعلی بدست می آید.

Year(Now())=

تابع()Weekday:

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

ساختار تابع()Weekday

Weekday(serial_number)=

4=Weekday(2019/05/01)

تابع()Weeknum:

این تابع تاریخی را به عنوان ورودی دریافت می کند و یک عدد صحیح مثبت که مشخص می کند تاریخ ذکر شده چندمین هفته از ابتدای سال می باشد را برمی گرداند.

ساختار تابع()Weeknum

Weeknum(serial_number)=

17=Weeknum(2019/04/25)

تابع عدد 17 که مشخص کننده 17 امین هفته اول سال می باشد را نشان می دهد .

تابع()Date:

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

ساختار تابع()Date

Date(year;month;day)=

برای مثال مقادیر سال و ماه و روز را به عنوان ورودی جداگانه به تابع Date می دهیم .

date(2019;05;16)=

و تابع خروجی 05/16/2019  را نشان می دهد .

نکته: عدد ماه در این تابع عددی بین 1 تا 12 باید باشد اگر بزرگتر از 12 وارد کنیم ابتدا مقدار بیشتر از 12 به عدد سال اضافه می شود و ما بقی آن به مقدار ماه اضافه می گردد.و اگر ماه عدد منفی بگیرد به همان میزان از سال کم و مقدار ماه به نسبت آن تغییر می کند .

برای مثال :ابتدا date(2019;12;10) را وارد می کنیم و تابع خروجی 2019/12/10 را بر می گرداند .

حالا اگر date(2019;16;10)  را وارد کنیم تابع خروجی 2020/04/10 را برمی گرداند یعنی اگر به ماه مقدار بزرگتر از 12 بدهیم مقدار اضافه آن را به سال اضافه می کند و بقیه را به ماه اعمال می کند .

حال اگر در مثال بالا مقدار 12 ماه را به 5- تغییر دهیم  خروجی تابع date(2019;-5;10)  بصورت 2018/7/10 می شود .

نکته : مقدار روز در این تابع مقدار بین 1 تا 31 می باشد اگر مقدار بیشتر از 31 را وارد کنیم سال و ماه و روز به نسبت آن تغییر می کنند .

برای مثال : date(2019;5;31) خروجی 2019/5/31 را بر می گرداند اگر مقدار روز را به 32 تغییر دهیم تابع مقدار 2019/6/1 را برمی گرداند و یک روز به ماه اضافه می کند و عدد روز هم تغییر می دهد.

اگر عدد روز خیلی بزرگ باشد سال هم تغییر می کند date(2019;5;256)  می شود 2020/01/11

نکته:مقدار سال باید عددی بین 1900(تاریخ مبدا در اکسل) تا 9999 باشد اگر کمتر از این مقدار وارد کنیم اکسل مقدار سال را با عدد 1900 جمع می کند .

برای مثال:

date(1852;12;6)=

چون مقدار سال 1852 از تاریخ مبدا اکسل کمتر می باشد عدد 1900 با 1852 جمع می شود و خروجی به شکل زیر می شود.

3752/12/6=

 تابع()Edate

تابع edate تاریخ چند ماه آینده و یا گذشته تاریخ فعلی را بر می گرداند این تابع دو آرگومان ورودی دارد :

1. آرگومان اول (start date): تاریخی را به عنوان تاریخ شروع دریافت می کند.
2. آرگومان دوم(months): عددی مثبت و یا منفی است که معرف ماه است  .

ساختار تابع()Edate

EDate(start_date;months)

2019/09/25=Edate(2019/04/25;5)

تابع به تاریخ فوق 5 ماه اضافه می کند و 2019/09/25 را نشان می دهد .

اگر عدد 13 را برای ماه در نظر بگیریم هم عدد سال تغییر می کند و 2020 نشان می دهد و هم ماه 

2020/05/25=Edate(2019/04/25;13)

2019/03/25=Edate(2019/04/25;-1)

تابع ()Eomonth

 همانند تابع Edate تاریخ را به عنوان ورودی دریافت می کند و آخرین روز تاریخ آینده را بر می گرداند و این تابع دو آرگومان ورودی دارد :

1. آرگومان اول (Start_date):تاریخی را به عنوان تاریخ شروع می گیرد .
2. آگومان دوم (Months):عددی مثبت یا منفی است که معرف ماه است و این عدد از تاریخ شروع کم یا اضافه می شود.

ساختار تابع()Eomonth

Eomonth(start_date;months)=

این تابع 3 ماه به تاریخ 2019/04/25 اضافه می کند و عدد ماه 7 را بر می گرداند و این تابع همیشه  آخرین روز ماه را بر می گرداند پس بجای عدد روز 25 عدد 31 نمایش داده می شود.

2019/07/31=Eomonth(2019/04/25;3)

2020/04/30=Eomonth(2019/04/25;12)

2018/11/30=Eomonth(2019/04/25;-5)

تابع()DateValue:

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

ساختار تابع()DateValue

DateValue(Data_text)=

برای مثال مقادیر سال و ماه و روز را به صورت متنی به تابع Date می دهیم .

datevalue(2019;05;16)=

و تابع خروجی43601 را نمایش می دهد که با تغییر فرمت سلول ، تاریخ را به فرمت تاریخ می توان مشاهده کرد.

از تب Home  گروه Number  روی فلش Format Number کلیک می کنیم تا پنجره Format cell باز شود .

از تب number از قسمت Category گزینه Custom را انتخاب می کنیم و فرمت نمایش تاریخ را بصورت YYYY/MM/DD تغییر می دهیم پس حالا  بجای نمایش عدد 43601 که مربوط به تاریخ است خود تاریخ 2019/05/16  نشان داده می شود .

تابع ساعت در اکسل (آموزش توابع اکسل پیشرفته ) 

برای استفاده از توابع زمان ابتدا ساعتی را درخانه اکسل وارد می کنیم . برای ثبت خودکار ساعت در اکسل( ساعت فعلی سیستم )همزمان سه دکمه Ctrl و Shift و :  را با هم می فشاریم ساعت فعلی سیستم نمایش داده می شود .

1. تابع ()Hour

2. تابع ()Minute

3. تابع ()Second

4. تابع()Time

5. تابع()Timevalue

    

تابع()Hour:

این تابع ، زمان(5:30:14)  ویا عدد سریالی زمان  را به عنوان ورودی دریافت می کند و فقط عدد ساعت (1 تا 24 )را به عنوان خروجی بر می گرداند .

ساختار تابع()Hour

Hour(serial_number)=

5=Hour(5:35:12)

و یا می توانیم زمان فوق را از تب home گزینه Number Format از زیر منوی باز شده گزینه General را انتخاب می کنیم تا فرمت سریالی زمان را نشان دهد و سپس از تابع Hour استفاده می کنیم و همان مقدار 5 را بر می گرداند.

5=Hour(0.232778)

تابع()Minute:

این تابع زمان (15:12:55) و یا عدد سریالی آن را به عنوان ورودی دریافت می کند و فقط عدد دقیقه(0 تا 60) آن را به عنوان خروجی بر می گرداند .

ساختار تابع()Minute

(serial_number)Minute=

12=minute(15:12:55)

12=minute(0.633969907)

تابع()Second:

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

ساختار تابع()Second

Second(serial_number)=

35=Second(12:15:35)

تابع()Time:

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

ساختار تابع()Time

(hour;minute;second)Time=

time(12;30;22)=

خروجی این تابع 12:30:22می شود.

تابع()Timevalue:

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

ساختار تابع()Timevalue

(time_text)Timevalue=

Timevalue(“7:25”)=

خروجی تابع فوق عدد 0.309028  می شود که با تغییر فرمت متن  این خانه می توانیم بجای عدد مربوط به زمان ، فرمت ساعت را مشاهده کنیم. 

برای این کار :از تب Home گروه Number ، روی گزینه Format number کلیک و گزینه Time را انتخاب می کنیم تا عدد 0.309028 را به فرمت ساعت بصورت 7:25:00 نمایش دهد.