Trouble combining Google Spreadsheet time calculation with isblank function

Question

I'm currently using a Google Spreadsheet at work to track employee hours. I had been using a formula to calculate hours, with hours worked expressed as a time (e.g. 6 and a half hours shows as 6:30). My boss has asked that I change the hours worked total to numbers (e.g. 6.5 instead of 6:30).

I had been using the following formula to calculate hours worked in time (where B is the out time and A is the in-time):

=if(isblank(B1),"", MINUS (B1,A1))

For calculating hours worked expressed in numbers, I have been trying to use the following formula:

=(IF(A1 <= B1, 0, 1)+B1 - A1) * 24

Now, this formula works when inputted into C1, but I'd like C1 to read as blank ("") until a value is inputted into B1, because otherwise it will show a value once I put something into A1.

Can anyone lend some insight?

I realized I misstated the second formula. It should be: =(IF(A1 <= B1, 0, 1)+B1 - A1) * 24 Shouldn't have read C1, as I'd been inputting the formula into C1, which would have resulted in a circular argument. — shatov

Unknown Unknown · Accepted Answer · 2015-04-08T01:28:14

You should be able to check that there are three values with COUNT(A1:C1).

=if(COUNT(A1:C1)<3, "", (IF(C1 <= B1, 0, 1)+B1 - A1) * 24)

Trouble combining Google Spreadsheet time calculation with isblank function

1 Answers