MrExcel.com - Your One Stop for Excel Tips & Solutions

Reformatting time

Posted by Ed on December 27, 2000 12:55 PM

Reformatting time

I have a task that is paid for by the hour expressed in four decimal places. I log the departure time and the return time. The person that previously did this would then handle the function of subtracting the time then converting the minutes to decimal with a calculator then enter the data manually. I’m not finished yet, I get to bill for the time plus 5 minuets. When I did the subtracting in 11:55 format it did well. However when I try to add the 5 minutes or convert to 0.0000 format all I get is weird numbers (for example when I tried to change the answer to the correct format the answer changed to .1041 from 2:30.

Posted by cpod on December 27, 2000 1:26 PM


if departure time is in a1 and return time is in b1 then:

=(HOUR(B1)-HOUR(A1))+(MINUTE(B1)-MINUTE(A1)+5)/60

You will have to format the cells for 0.0000 format.