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!
