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

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

سلام.

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

ترفند شماره 1

یکی از مشکلات و چالش های کاربرانی که با محاسبات اختلاف ساعت ها سروکار دارند زمان های منفی است. مثلاً فرض کنید در کارگاه شما تعدادی از افراد شیفت شب هستند . این افراد مثلاٌ ساعت 21 شروع به کار می کنند و ساعت 7 صبح از محل کار خارج می شوند. برای محاسبه ساعت کارکرد معمولاً فرمول را به صورت زیر در سلول ساعت کارکرد وارد می کنید.

7:00-21:00 و چون نیجه این فرمول عددی منفی است سلول ها با علامت ######### پر می شوند و هرچه اندازه سلول را بزرگتر کنید مشکل همچنان پابرجاست!

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

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

فرض کنید ساعت ورود برابر A و ساعت خروج برابر B باشد

=if(B<A,1+B-A,B-A)

یعنی اگر ساعت خروج کوچکتر از ساعت ورود باشد آنگاه عدد 1 را به ساعت خروج اضافه کرده و سپس ساعت ورود را از آن کم می کنیم در غیر این صورت فقط ساعت ورود را از ساعت خروج کم می کنیم.

• راه حل ساده تر آن است که از تابع MOD به شکل زیر استفاده کنیم

=MOD(B-A,1)

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

تذکر: در بعضی از سیستم ها ممکن است به جای کاما (,) از نقطه کاما (;) استفاده شود (بستگی به فرمت ویندوز شما دارد که از قسمت Control Panel قابل تغییر است)

حالت های مختلف را در شکل زیر ببینید

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

ترفند شماره 2

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

فرض کنید شخصی 10 ساعت کارکرد دارد و حقوق ساعتی او 1000 تومان می باشد. انتظار داریم وقتی 10 ساعت را در حقوق ساعتی ضرب می کنیم مبلغ 10000 تومان بدست آید، اما با کمال تعجب می بینید که نتیجه  برابر 416.67 می باشد.

برای حل مشکل باید عدد بدست آمده را در 24 ضرب کنید

یعنی محاسبات باید به این شکل نوشته شود

10:0*1000*24

(علت را پیدا کنید)

ترفند شماره 3

وقتی که قصد دارید عددی کسری را در یک یک سلول وارد کنید، اکسل آن را به تاریخ میلادی تبدیل می کند.

مثلاً با وارد کردن کسر 5/4  نتیجه نمایش داده شده به این شکل تبدیل می شود. 5-Aprیعنی تاریخ پنجم آوریل را نمایش می دهد.

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

ترفند شماره 4

هنگام جمع زدن ساعت های کارکرد که مقدار آنها از 24 ساعت بیشتر می شود، اکسل همیشه عدد 24 ساعت را کسر کرده و باقیمانده را نشان می دهد. مثلاً اگر جمع ساعت های کارکرد 31 ساعت شود، نتیجه به صورت 7:00 نمایش داده می شود. برای نمایش نتیجه صحیح پس از ورود به پنجره Format Cells فرمت Custom را انتخاب کرده و فرمت را به صورت زیر در کادر Type وارد نمایید.

[h]:mm

یعنی در اطراف ساعت باید از یک جفت براکت (کروشه) استفاده کنید

شکل های زیر را ببینید

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

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