Excel Automatically Converting MM:SS to Clock Time

In this tutorial, we will learn how to automatically convert minutes and seconds (MM:SS) to clock time in Excel using formulas and formatting options.

To start, insert the formula `=TEXT(F9/86400,'hh:mm:ss')` in cell F9 and press Enter. Then, drag the Fill Handle tool to the right to apply the same formula to other cells.

Next, we will calculate the total normal time and total overtime by using the TEXT function with the SUM function. To do this, type the following formula in cell F4: `=SUM(TEXT(C4:C8,'mm:ss'))`. Here, select the cell range C4:C8 to specify the cells that you want to add.

Another way to calculate the total time is by using the TIME function with arithmetic calculations. To do this, type the following formula in cell D4: `=TIME(HOUR(C4),MINUTE(C4),SECOND(C4))+TIME(HOUR(E4),MINUTE(E4),SECOND(E4))`. Here, you can add time under 24 hours by using the TIME function and arithmetic calculations.

To get only the seconds from a selected cell time, go to the 'Number' group in the Home tab and select the 'Custom' format option. Then, type 'ss' to get only the seconds.

Similarly, you can convert the selected cell time into minutes or hours by changing the format using the ribbon.

You can also use the IF function to calculate the negative time difference. To do this, type the following formula in cell E4: `=IF(D4-C4<0,TEXT(ABS(D4-C4),'hh:mm'),D4-C4)`. Here, the logical_test value becomes true when the result is negative, and the IF function returns the time difference otherwise.

Finally, you can convert the selected cell range time into an hour, minute, and second format using the 'Custom' format option. To do this, select the cell or cell range that you want to change the format, go to the Home tab > Number group > Select the Number Format icon, and then type 'hh:mm:ss'.

By following these steps, you can easily convert minutes and seconds (MM:SS) to clock time in Excel using formulas and formatting options.

Excel automatically converting mmss to clock time