This tutorial will cover several methods in Excel for Time Conversion. We’ll walk you through converting time into hours, minutes and seconds. You will also learn to convert text into time, split and spell operation of time.
Time conversion means converting time values from one format to another. Time conversion is useful for planning and managing events across various time zones. We can also analyze time-related data like work hours.
After going through this context, you can improve your skills for time converting. So, let’s get started.
Download Practice Workbook
You can download the practice workbook by clicking the link below.
How to Convert Time to Decimal Numbers in Excel
Example 1: Covert Time to Hours
Method 1: Use Arithmetic Formula
Select cell C5 >> apply the following formula >> drag the Fill Handle icon to C10.
=B5*24
When using the Arithmetic formula to convert time data, multiply by 24 for Hours.
Method 2: Use CONVERT Function
Choose the C5 cell >> apply the below formula >> drag the Fill Handle icon to C10.
=CONVERT(B5,"day","hr")
Method 3: Use HOUR, MINUTE and SECOND Functions
Select the C5 cell >> insert the below formula >> drag Fill Handle to C10.
=HOUR(B5) + MINUTE(B5)/60+ SECOND(B5)/3600
Example 2: Covert Time to Minutes
Method 1: Use Arithmetic Formula
Select cell C5 >> apply the below formula >> drag the Fill Handle icon to C10.
=B5*1440
When using the Arithmetic formula to convert time data, multiply by 1440 for Minutes.
Method 2: Use CONVERT Function
Choose the C5 cell >> apply the below formula >> drag the Fill Handle icon to C10.
=CONVERT(B5,"day","mn")
Method 3: Use HOUR, MINUTE and SECOND Functions
Select the C5 cell >> input the below formula >> drag Fill Handle to C10.
=HOUR(B5)*60 + MINUTE(B5) + SECOND(B5)/60
Example 3: Covert Time to Seconds
Method 1: Use Arithmetic Formula
Select the C5 cell >> insert the below formula >> drag the Fill Handle icon to C10.
=B5*86400
When using the Arithmetic formula to convert time data, multiply by 86400 for Seconds.
Method 2: Use CONVERT Function
Choose cell C5 >> apply the below formula >> drag the Fill Handle icon to cell C10.
=CONVERT(B5,"day","sec")
Method 3: Use HOUR, MINUTE and SECOND Functions
Select the C5 cell >> apply the below formula >> drag the Fill Handle icon to C10.
=HOUR(B5)*3600 + MINUTE(B5)*60 + SECOND(B5)
How to Convert Time from One Unit to Another in Excel
Example 1: Convert Days to Hours
Choose the C7 cell >> apply the below formula >> hit Enter.
=CONVERT($B$7,"day","hr")
Example 2: Convert Hours to Minutes
Choose cell C10 >> insert the below formula >> hit Enter.
=CONVERT($B$10,"hr","mn")
Example 3: Convert Years to Days
Select the C7 cell >> apply the below formula >> hit Enter.
=CONVERT($B$7,"yr","day")
How to Convert Decimal Number to Standard Time in Excel
Choose cell C5 >> insert the below formula >> drag Fill Handle to C10.
=TEXT(B5/24,"h:mm AM/PM")
Read More: How to Convert Time to Decimal in Excel
How to Convert Time to Text in Excel
Select the C5 cell >> insert the following formula >> drag the Fill Handle icon to C10.
=TEXT(B5,"h:mm:ss")
How to Convert Text to Time in Excel
Choose the C5 cell >> apply the below formula >> drag Fill Handle to C10.
=TIMEVALUE(B5)
How to Convert Time to Time Zone in Excel
Choose cell C11 >> apply the following formula >> press Enter.
=MOD(C5+(B8/24),1)
How to Split Date and Time in Excel
Choose the C5 cell >> apply the below formula >> drag the Fill Handle icon to C9.
=INT(B5)
Select cell C3 >> insert the below formula >> drag Fill Handle to C9.
=MOD(B5,1)
How to Spell Time in Excel
Choose the C7 cell >> insert the following formula >> drag the Fill Handle icon to C11.
=INT(B7-$C$4) & " days, " & HOUR(B7-$C$4) & " hours, " & MINUTE(B7-$C$4) & " minutes and " & SECOND(B7-$C$4) & " seconds"
Things to Remember
- To calculate time duration, ensure the current time is smaller than the time from which you want to calculate duration.
- When converting Time into Hours, Minutes, and Seconds, ensure the format of the cells is kept General.
Frequently Asked Questions
- Can we convert time in Excel?
Yes, we can. Excel offers various functions and features to let us change time formats. We also can calculate durations and work with different time zones.
- How do I convert time spent in Excel?
Select the cell or range containing the time data to convert time spent in Excel. Later, press Ctrl+1 and navigate to the Number tab. Now choose Time or Custom and select the desired time format. Click OK to apply the new form.
- How do I convert HH mm to hours in Excel?
Using a simple formula, we can convert HH mm format to hours in Excel. Assume time data is in A1. Now apply =HOUR(A1)+MINUTE(A1)/60 in a desired cell and press Enter.
Excel Time Conversion: Knowledge Hub
Conclusion
Learning about various ways in Excel for Time Conversion is essential for handling time and date data types. We guided you through this article with basic to advanced topics. This post provides practical examples related to converting time in Excel. Visit our ExcelDemy page to learn more about the various aspects of Excel. You can also post your Excel problem in our ExcelDemy Forum.
<< Go Back to Date-Time in Excel | Learn Excel
