Excel Tip: Convert time to a serial value in Microsoft Excel / Microsoft Excel Tips / ExcelTip.com

Tip Printed from ExcelTip.com

Convert time to a serial value in Microsoft Excel

To covert time into a serial (numerical) value, enter in cell A1: 2342 (this represents the time 23:42)

Enter the following formula in cell B1: =(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2))+0

The Result: 0.9875

Please note: The serial value for time ranges between 0 to 1; midnight=0, 24 hours=1