آموزش تابع TIMEVALUE در اکسل
معرفی تابع TIMEVALUE
تابع TIMEVALUE در اکسل برای تبدیل «یک متنِ زمان» (مثل “08:30” یا “5:45 PM”) به «عدد زمانِ واقعی اکسل» استفاده میشود. اکسل زمان را به شکل یک عدد اعشاری ذخیره میکند (بخشی از یک روز): مثلاً 12:00 ظهر برابر 0.5 است، چون نصف روز گذشته است.
این تابع وقتی خیلی کاربردی میشود که زمانها به صورت متن وارد شده باشند (مثلاً از نرمافزار دیگری کپی شده باشند، یا با فرمت اشتباه ذخیره شده باشند) و شما بخواهید روی آنها محاسبه انجام دهید: جمع مدتها، مقایسه زمانها، محاسبه تاخیر، شیفت کاری و…
مثال ساده: اگر در سلول A1 متن “08:30” را داشته باشید، TIMEVALUE آن را به زمان قابل محاسبه تبدیل میکند.
=TIMEVALUE("08:30")
خروجی این فرمول یک عدد زمان است که اگر فرمت سلول را روی Time بگذارید، همان 08:30 نمایش داده میشود.
کاربردهای اصلی تابع TIMEVALUE
- تبدیل زمانهای متنی به زمان واقعی برای محاسبه و گزارشگیری
- محاسبه اختلاف بین دو زمان (مثلاً ورود و خروج)
- استانداردسازی دادههایی که از سیستمهای دیگر وارد اکسل شدهاند
- استفاده در فرمولهای شرطی (IF/AND/OR) برای بررسی دیرکرد یا بازههای زمانی
- آمادهسازی داده برای نمودارها و داشبوردهای زمانی
ساختار (Syntax)
=TIMEVALUE(text_time)
=TIMEVALUE(زمان_به_صورت_متن)
آرگومانها
text_time / زمان_به_صورت_متن
رشتهای متنی که زمان را نشان میدهد؛ مثل “14:25″، “8:10 PM” یا مقداری که در یک سلول به شکل متن ذخیره شده است. خروجی تابع یک «عدد سریال زمان» است که با فرمتدهی (Time) مثل ساعت نمایش داده میشود.
مثالهای ساده و پایه
مثال 1: تبدیل زمان متنی داخل سلول
فرض کنید در A2 مقدار زیر به شکل متن ذخیره شده است: 09:15
=TIMEVALUE(A2)
نتیجه: یک مقدار زمانی واقعی. اگر فرمت سلول را Time بگذارید، 09:15 نمایش داده میشود و میتوانید آن را از زمانهای دیگر کم/زیاد کنید.
مثال 2: محاسبه اختلاف دو زمان متنی
فرض کنید:
- A2 = “08:00” (متن)
- B2 = “16:30” (متن)
برای محاسبه مدت کار (اختلاف خروج – ورود):
=TIMEVALUE(B2)-TIMEVALUE(A2)
نتیجه یک مقدار زمان (مثلاً 08:30). برای نمایش بهتر، فرمت سلول را روی [h]:mm بگذارید تا مدتها بهدرستی نمایش داده شوند.
مثال 3: تبدیل متنِ دارای ثانیه
اگر A2 شامل “10:05:30” باشد:
=TIMEVALUE(A2)
نتیجه: زمان واقعی با دقت ثانیه (اگر فرمت نمایش شامل ثانیه باشد).
مثالهای کاربردی و واقعی
مثال 1: جمع مدتزمانها با SUM (وقتی دادهها متنی هستند)
فرض کنید در ستون A مدتزمانها به صورت متن وارد شدهاند:
- A2=”01:15″
- A3=”02:20″
- A4=”00:45″
برای جمع کل:
=SUM(TIMEVALUE(A2),TIMEVALUE(A3),TIMEVALUE(A4))
نکته: اگر مجموع از 24 ساعت بیشتر شد، فرمت نمایش را روی [h]:mm قرار دهید.
مثال 2: بررسی دیرکرد با AND (ورود بعد از 09:00)
فرض کنید A2 زمان ورود به شکل متن است (مثلاً “09:12”). میخواهیم اگر بعد از 09:00 بود، نتیجه “دیرکرد” بدهد.
=IF(AND(TIMEVALUE(A2)>TIMEVALUE("09:00"),A2""),"دیرکرد","بهموقع")
نتیجه: اگر زمان ورود بزرگتر از 09:00 باشد و سلول خالی نباشد، “دیرکرد” نمایش داده میشود.
مثال 3: تشخیص شیفت کاری با OR (شیفت صبح یا عصر)
فرض کنید A2 زمان ورود (متنی) است. اگر ورود بین 07:00 تا 09:00 باشد شیفت صبح، و اگر بین 15:00 تا 17:00 باشد شیفت عصر.
=IF(OR(AND(TIMEVALUE(A2)>=TIMEVALUE("07:00"),TIMEVALUE(A2)=TIMEVALUE("15:00"),TIMEVALUE(A2)<=TIMEVALUE("17:00"))),"در بازه شیفت","خارج از شیفت")
مثال 4: شمارش زمانهای بعد از یک ساعت مشخص با COUNTIF (وقتی واقعی هستند) + راهحل وقتی متنی هستند
COUNTIF وقتی زمانها «واقعی» باشند عالی کار میکند. اگر ستون A زمان واقعی باشد:
=COUNTIF(A:A,">"&TIMEVALUE("18:00"))
اگر زمانها متنی باشند، بهتر است ابتدا ستون کمکی بسازید:
- B2: تبدیل به زمان واقعی
=TIMEVALUE(A2)
و سپس COUNTIF را روی ستون B اجرا کنید.
مثال 5: ترکیب با XLOOKUP برای تبدیل زمانِ متنی خروجی جستجو
فرض کنید در جدول شما، ستون D زمان شروع شیفت را به صورت متن نگه داشته و شما با XLOOKUP آن را برمیگردانید، اما میخواهید خروجی زمان واقعی باشد:
=TIMEVALUE(XLOOKUP(A2,Employees[Code],Employees[ShiftStart]))
نتیجه: زمان شیفت به شکل قابل محاسبه برمیگردد (نه متن).
ترکیب تابع TIMEVALUE با فرمولهای دیگر
- TIMEVALUE + IF برای شرطگذاری روی زمان متنی
=IF(TIMEVALUE(A2)>TIMEVALUE("09:00"),"دیرکرد","مجاز") - TIMEVALUE + SUM برای جمع مدتهای متنی
=SUM(TIMEVALUE(A2),TIMEVALUE(A3))
- TIMEVALUE + TEXT برای نمایش استاندارد (وقتی خروجی عددی است)
=TEXT(TIMEVALUE(A2),"hh:mm")
- TIMEVALUE + IFERROR برای مدیریت ورودیهای نامعتبر
=IFERROR(TIMEVALUE(A2),"زمان نامعتبر")
- TIMEVALUE + (محاسبه مدت) برای اختلاف خروج و ورود متنی
=TIMEVALUE(B2)-TIMEVALUE(A2)
خطاهای رایج و روش رفع آنها
1) خطای #VALUE!
علت: متنِ ورودی قابل تشخیص به عنوان زمان نیست (مثلاً “8-30” یا “ساعت 9”).
راهحل: ورودی را به فرمتهای استاندارد تغییر دهید (مثل “08:30” یا “8:30 PM”) یا از IFERROR برای کنترل خروجی استفاده کنید.
=IFERROR(TIMEVALUE(A2),"زمان نامعتبر")
2) زمان درست است اما نتیجه به شکل عدد اعشاری نمایش داده میشود
علت: خروجی TIMEVALUE عدد زمان است و اگر فرمت سلول General باشد، مثل 0.35416 نمایش داده میشود.
راهحل: فرمت سلول را روی Time (مثلاً hh:mm) بگذارید یا با TEXT نمایش را کنترل کنید.
=TEXT(TIMEVALUE(A2),"hh:mm")
3) جمع مدتها اشتباه نمایش داده میشود (بعد از 24 ساعت ریست میشود)
علت: فرمت زمان معمولی (h:mm) بعد از 24 ساعت دوباره از صفر نمایش میدهد.
راهحل: از فرمت سفارشی [h]:mm برای نمایش مجموع مدتها استفاده کنید.
4) مشکل بهخاطر تنظیمات منطقهای (AM/PM یا جداکنندهها)
علت: بعضی سیستمها قالبهای خاصی را بهتر تشخیص میدهند. ممکن است “5:30 PM” در یک تنظیمات درست باشد و در دیگری مشکل ایجاد کند.
راهحل: از فرمت 24 ساعته مثل “17:30” استفاده کنید یا داده را قبل از ورود استاندارد کنید.
5) وجود فاصلههای اضافی در متن زمان
علت: دادههای کپیشده گاهی فاصله اضافی دارند و باعث تشخیص نادرست میشوند.
راهحل: ابتدا TRIM و سپس TIMEVALUE را اعمال کنید.
=TIMEVALUE(TRIM(A2))
نکات حرفهای و ترفندهای مهم
- اگر با «مدت زمان» سروکار دارید (نه ساعتِ روز)، برای نمایش درست مجموعها از فرمت [h]:mm یا [h]:mm:ss استفاده کنید.
- برای جلوگیری از خطا در گزارشها، TIMEVALUE را با IFERROR ترکیب کنید تا دادههای خراب، کل محاسبات را بههم نریزند.
=IFERROR(TIMEVALUE(A2),0)
- وقتی زمان به صورت متن همراه تاریخ باشد (مثلاً “2025/01/10 08:30”)، TIMEVALUE معمولاً بخش زمان را استخراج میکند، اما اگر فرمتها نامنظم باشند بهتر است ابتدا داده را پاکسازی کنید.
- اگر داده شما «زمان واقعی» است و فقط ظاهرش شبیه متن است، اول با تغییر فرمت سلول یا بررسی با ISNUMBER مطمئن شوید. TIMEVALUE برای متن طراحی شده است.
=ISNUMBER(A2)
- برای داشبوردها، بهتر است ستون کمکی بسازید (متن زمان → زمان واقعی) و همه محاسبات را روی ستون تمیز انجام دهید.
تفاوت تابع TIMEVALUE با توابع مشابه
- TIMEVALUE: متنِ زمان را به عدد زمان تبدیل میکند. ورودی اصلیاش متن است.
- TIME: از ساعت، دقیقه، ثانیه عدد زمان میسازد (ورودی عددی است، نه متن).
=TIME(8,30,0)
- VALUE: متن عددی را به عدد تبدیل میکند؛ گاهی تاریخ/زمان را هم تبدیل میکند، اما همیشه قابل اتکا برای همه فرمتهای زمان نیست.
=VALUE("08:30") - TEXT: برعکس TIMEVALUE عمل میکند؛ یعنی عدد زمان را به متن با قالب دلخواه تبدیل میکند.
=TEXT(TIMEVALUE("08:30"),"hh:mm")
سازگاری با نسخههای مختلف اکسل
تابع TIMEVALUE از توابع قدیمی و پایه اکسل است و در اغلب نسخهها پشتیبانی میشود، از جمله:
- Excel 2007، 2010، 2013، 2016، 2019، 2021
- Microsoft 365 (Excel جدید)
- Excel برای Mac (نسخههای مدرن)
نکته مهم در سازگاری معمولاً مربوط به «تنظیمات منطقهای و قالب زمان» است، نه خود تابع. اگر دادههای شما از سیستم دیگری میآید، بهتر است زمانها را به فرمت 24 ساعته استاندارد کنید.
سؤالات پرتکرار درباره تابع TIMEVALUE
آیا TIMEVALUE تاریخ را هم برمیگرداند؟
خیر، فقط بخش زمان را به عدد زمان تبدیل میکند. اگر متن شامل تاریخ هم باشد، معمولاً فقط زمان را استخراج میکند.
چرا خروجی TIMEVALUE عدد اعشاری است؟
چون اکسل زمان را کسری از یک روز ذخیره میکند. با تغییر Format به Time، ساعت را میبینید.
برای محاسبه مدت بیشتر از 24 ساعت چه کار کنم؟
محاسبه درست انجام میشود، اما نمایش را باید با فرمت [h]:mm تنظیم کنید تا از 24 ساعت رد شود.
TIMEVALUE با زمان فارسی هم کار میکند؟
اگر ورودی شما یک متن استاندارد قابل فهم برای اکسل باشد (مثل 08:30) معمولاً مشکلی نیست. اما عباراتی مثل “۸:۳۰” یا “ساعت ۸ و نیم” ممکن است خطا بدهند و نیاز به تبدیل/پاکسازی دارند.
جمعبندی و پیشنهاد یادگیری بعدی
تابع TIMEVALUE ابزار اصلی شما برای تبدیل «زمانِ متنی» به «زمانِ قابل محاسبه» در اکسل است. با این تبدیل، میتوانید اختلاف زمانها را حساب کنید، مدتها را جمع بزنید، گزارش دیرکرد بسازید و دادههای واردشده از سیستمهای دیگر را استاندارد کنید.
برای یادگیری بعدی، پیشنهاد میشود این مباحث را دنبال کنید:
- فرمتدهی حرفهای زمان و مدت: h:mm، [h]:mm، [h]:mm:ss
- توابع مرتبط: TIME، HOUR، MINUTE، SECOND
- محاسبات حضور و غیاب با IF، AND، OR و IFERROR
- پاکسازی دادههای متنی با TRIM، SUBSTITUTE و VALUE