فرمول تبدیل اعدد صحیح به زمان صحیح (ساعت:دقیقه) در اکسل

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

در این مقاله این فرمول‌ها را بررسی خواهیم کرد.

فرمول تبدیل عدد دقیقه به ساعت صحیح در اکسل

خروجی کسرکار نهایی ماهانه (کارکرد) یک نرم افزار حسابداری (در اینجا دقیقا راهکاران سیستم) عددی به شکل 480 دقیقه است و نیاز داریم تا آن را به ساعت در اکسل تبدیل کنیم تا بتوانیم سایر محاسبات را بر روی آن انجام دهیم:

minutes
480

قدم اول) برای اینکار کافی است که این عدد را بر 60 تقسیم کنیم و تا مشخص شود که چند ساعت و چه کسری از ساعت (دقیقه) خواهد شد یعنی اگر فردی 480 دقیقه کار کرده است، او 8 ساعت کار کرده است.

= 480 / 60   --->   8

مثال ۲) حال مثال دیگری را در نظر بگیرید که فردی 510 دقیقه کار کرده است بنابراین او «هشت و نیم ساعت» کار کرده است:

= 510 / 60   --->   8.5

مثال ۳) حال فردی دیگری 2430 دقیقه کار کرده است و با همین محاسبه او «چهل و نیم ساعت» کار کرده است:

= 2430 / 60   --->   40.5

ممکن است تا همین جا شما بتوانید این عدد را با سایر اعدادی که دارید (چون آن اعداد هم دقیقا همین شکل هستند) جمع/ تفریق کنید.

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

در اینجا نیاز است که شما مقاله «مفهوم تاریخ و زمان در اکسل» را به دقت بخوانید. زیرا اکسل زمان‌های بیش از 24 ساعت را به صورت روز در نظر می‌گیرد.

مثلا همان که فردی که 2430 دقیقه کار کرده بود (یعنی چهل و نیم ساعت) از نظر اکسل او «یک روز و شانزده نیم ساعت» کار کرده است و باید ما این زمان را در اکسل به شکل «روز - ساعت - دقیقه » در آوریم تا از نظر اکسل یک زمان معتبر اکسلی تلقی شود.

فرمول آن به شکل کلی زیر خواهد شد:

= minutes / (60 * 24)

و سپس Format Cells سلولها را بر روی گزینه Custom --> [hh]:mm:dd تنظیم می‌کنیم و زمان ما در اکسل به شکل صحیح اکسل خواهد شد:

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

در مثال‌های قبلی زمان به صورت دقیقه بود. اگر بخواهیم که عددی که بیانگر «ساعت» است را به شکل زمان صحیح اکسل به صورت ساعت:دقیقه بنویسیم کافی است که از فرمول زیر استفاده کنیم:

= hours / 24

و سپس Format Cells را روی گزینه Custom --> [hh]:mm:dd تنظیم شود.

فرمول تبدیل زمان اکسل (ساعت:دقیقه ) به یک عدد 

گاهی نیاز است تا زمان اکسل را که به صورت ساعت:دقیقه است را به یک عدد تبدیل کنیم، برای اینکار کافی است که فرمول‌های قبلی را معکوس کنیم.

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

فرض کنید در سلول A1 اکسل یک زمان به شکل صحیح اکسلی وارد شده است، بنابراین فرمول ما به شکل زیر است:

= A1 * 24 * 60

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

فرض کنید در سلول A1 اکسل یک زمان به شکل صحیح اکسلی وارد شده است، بنابراین فرمول ما به شکل زیر است:

= A1 * 24

دیتا تایپ Duration در Power Query

ما در Power Query یک دیتا تایپ ویژه برای زمان داریم به نام Duration که برای زمان‌هایی که است بیش از ۲۴ ساعت است.  برای نمونه مثال زیر را می‌توانید ببینید:

= {
#duration(0, 10, 0, 0),  // 10:00:22
#duration(0, 16, 30, 0), // 16:30
#duration(1, 0, 0, 0),   // 1 day
#duration(2, 10, 5, 0)   // 2 days & 10:05:00
    }

فرمول Power Query تبدیل عدد دقیقه به زمان اکسل به صورت ساعت:دقیقه 

= Duration.From( [minutes] / (24 * 60))

فرمول Power Query تبدیل عدد ساعت به زمان اکسل به صورت ساعت:دقیقه 

= Duration.From( [hours] / 24 * 60)

تبدیل زمان اکسلی در Power Query به روز یا ساعت یا دقیقه

در هنگام خواندن دیتا‌هایی که بیش از ۲۴ ساعت است باید حتما در Power Query دیتا تایپ آن ستون را (اگر وجود دارد ابتدا پاک کنید) و سپس بر روی Duration تنظیم کنیم.

حال با تابع زیر می‌توانیم آن زمان را به صورت روز و کسری از روز بنویسیم:

= Duration.TotalDays( [excel_time] )

و با تابع زیر می‌توانیم آن زمان را به صورت ساعت و کسری از ساعت بنویسیم:

= Duration.TotalHours( [excel_time] )

و با تابع زیر می‌توانیم  آن زمان را به صورت دقیقه و کسری از دقیقه بنویسیم:

= Duration.TotalMinutes( [excel_time] )

و خروجی این کوئری به شکل زیر خواهد شد: