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

توابع تاریخ و زمان در اکسل
تعریف مفاهیم زمان و تاریخ در اکسل
روش محاسبه ساعت دقیقه و روز در اکسل و تغییر فرمت خانه ها
توابع تاریخ و روش های کار با این توابع بهمراه مثال
توابع زمان و روش های کاربرد این توابع بهمراه مثال های کاربردی
توابع تاریخ و ساعت در اکسل (Date and time Function)
مفاهیم تاریخ و زمان در اکسل
نرم افزار اکسل ، تاریخ و زمان را بصورت عدد درک می کند و در تمام محاسباتش هم مانند عدد در نظر می گیرد . و ما با تغییر فرمت سلول ها این اعداد را بفرمت تاریخ و زمان می توانیم مشاهد کنیم .
براي آشنايي با کاربرد و محاسبات تاريخ و ساعت در ليست حقوق و دستمزد پرسنل از بسته کاربرد اکسل در حسابداري مي توانيد استفاده نماييد .
برای مثال اگر در خانه ای که تاریخ یا ساعت وارد شده فرمت سلول را تغییر دهیم برای تغییر فرمت سلول در اکسل از تب Format گزینه Format cell را انتخاب و روی General کلیک می کنیم بجای نمایش تاریخ و یا ساعت ، عدد سریالی صحیح یا اعشاری مشاهده می کنید.
نکته : قسمت صحیح این عدد سریالی بیانگر تاریخ و قسمت اعشاری عدد بیانگر ساعت ، دقیقه و ثانیه می باشد .
یعنی اگر ساعت در خانه های اکسل وارد کنیم و فرمت آن را به 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 که در قسمت های بعدی توضیح خواهم داد ، استفاده کنیم .
تابع ()year
تابع ()Mounth
تابع ()day
تابع()Days
تابع()Networkdays
تابع()Weekday
تابع()Today
تابع()Now
تابع ()Date
تابع()EDate
تابعEomonth
تابع()Datevalue
تابع()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=
تابع()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 تاریخ چند ماه آینده و یا گذشته تاریخ فعلی را بر می گرداند این تابع دو آرگومان ورودی دارد :
- آرگومان اول (start date): تاریخی را به عنوان تاریخ شروع دریافت می کند.
- آرگومان دوم(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 تاریخ را به عنوان ورودی دریافت می کند و آخرین روز تاریخ آینده را بر می گرداند و این تابع دو آرگومان ورودی دارد :
- آرگومان اول (Start_date):تاریخی را به عنوان تاریخ شروع می گیرد .
- آگومان دوم (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 و : را با هم می فشاریم ساعت فعلی سیستم نمایش داده می شود .
تابع ()Hour
تابع ()Minute
تابع ()Second
تابع()Time
تابع()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 نمایش دهد.

مطالب زیر را حتما مطالعه کنید
6 دیدگاه
به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.
خوب بود نیاز به مثال های بیشتر و کار بردی دارد
تشکر از شما جهت تهیه
سلام خيلي عالي بود
بسيار تشکر
سلام با تشکر از آموزشهاي مفيد شما
لطفا تابعي را معرفي بفرماييد که تابع تاريخ را به عنوان ورودي دريافت کند و عدد روز را برگرداند
ممنون
با سلام
مي توانيد از تابع ()Day استفاده نماييد تاريخ را به عنوان ورودي دريافت و عدد روز را برمي گرداند
براي آموزش هاي کامل تر از لينک https://www.ostadamooz.com/product/excel-tutorials/ استفاده نماييد
با تشکر
با سلام چطور مي تونم اطلاعات سلول يا محدوده اي از سلاول ها فقط خواندني بشن و قابليت تغيير محتوا از افراد گرفته شود
با تشکر
با سلام
کاربر محترم مي توانيد از لينک https://www.ostadamooz.com/manage-sheet-excel/ استفاده نماييد کامل توضيح داده شده است
با سپاس