HOUR(IF(AND( Formula Help... Please!!

[XL1980]

Hi,

I am having problems combining the HOUR, IF and AND functions in a formula and I'm hoping someone can assist.

In cell A1, I have 09:00 and in cell A2 I have 17:00. Cell A3 has the following formula: =HOUR(A2-A1-1). This gives me 7.00 hours which is what I want. The '-1' is used to subtract a lunch hour.

What I want cell A3 to do is to only calculate the number of hours-1 when there is ONLY a value in both A1 & A2. If there isn't a value in cells A1 & A2 and I use the formula =HOUR(A2-A1-1), it gives me -1.00 (obviously).

I have tried the following formula in cell A3: =HOUR(IF(AND(A1<>"",A2<>""),A2-A1-1,"")). The answer I get is #VALUE!

Then, if I enter in cell A1 09:00 and in cell A2 17:00, cell A3 shows #NUM!.

I've exhausted searching on Google. Can anyone help?

Thank you.

 

[AlanY]

try

=IF(COUNT(A1:A2)=2,HOUR(A2-A1)-1,"")

 

[XL1980]

Thank you AlanY. Much appreciated!

 

[AlanY]

You're welcome

 

[davidamiri]

Nice one Alan.