MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Calculating time


Posted by Karen on December 29, 2000 8:33 AM

I am trying to set a formula that will calculate time on a timesheet. I have an in column and an out column. I want the cells to read the time as am/pm. For example, in cell b3 (in) I have 8:30am. In cell c3 (out) I have 8:00pm. How do I get the formula (in cell b4) to tell me how many hours were worked, in a simple format such as 11.5 as opposed to 11:30? Also, I would like for the cell (b4) to be blank if no hours were worked instead of printing (0). Can anyone help? I'm desperate.


Posted by cpod on December 29, 2000 8:56 AM


Try this:

=(HOUR(C3)-HOUR(B3))+(MINUTE(C3)-MINUTE(B3))/60

And format at a number rather than time.

Posted by Ed Acosta on December 29, 2000 10:42 AM

To eliminate a 0 showing up if no time is worked then enter it like this:

=IF(ISBLANK(B3),"",(HOUR(C3)-HOUR(B3))+(MINUTE(C3)-MINUTE(B3))/60)

This assumes cell B3 is left blank if no start time is entered.

Posted by Karen on December 29, 2000 11:20 AM

THANK YOU, THANK YOU, AND 1,000 TIMES THANK YOU! I worked on that formula for two days and was about to pull my hair out from frustration. I entered it into my cell and it worked just exactly the way I wanted it to. Again, thank you very much!