IF function in Excel

[Yorkie580]

I'm trying to write an IF function in Excel. It all works fine except for when the cells are empty. Basically it's a spreadsheet to calculate hours worked. The formaula is basically set up so that if the end time is shown as earlier than the start time, it is working fine and is giving the correct answers, however it shows an error when there are no entries (ie the person hasn't worked).

The formual I've done is as follows:

=IF(C11>B11,TEXT(C11-B11,"hh:mm"),IF(C11<B11,
TEXT(C11+(24-B11),"hh:mm"),IF(C11=B11,IF(C11=
BLANK,"00:00","24:00"))))


I know that the error is the last part (and I made a guess at the BLANK part). I'm basically trying to say that if C11 and B11 are identical, it must be 24hrs, otherwise I want a total of "00:00" shown.

Please help....

[Yorkie580]

My question in a nutshell, is how do I write a logical test that will give an answer of 24hrs if the cells are populated and equal, and will give an answer of 0 is the cells are blank?

[ChuckFickens]

try

=IF(C11=B11, "0", (C11>B11~TEXT(C11-B11,"hh:mm")~IF(C11<B11,TEX
T(C11+(24-B11),"hh:mm"),IF(C11=B11,IF(C11=bla
nk,"00:00","24:00")))))


(I've only got open office to hand but the if statements shouldn't work any different)

[Old_Geezer]

No expert, but try ISBLANK(C11) see if it works.

[ChuckFickens]

oh, take that back, it doesn't work!

[MarkRae]

The problem is indeed with BLANK, as that's not a recognised Excel keyword - change it to "" and you'll be fine. Also, you have a parenthesis missing at the end.


> I'm basically trying to say that if C11 and B11 are identical, it must be 24hrs, otherwise I want a total of "00:00" shown.

Unless you have other requirements which you haven't mentioned, I think your formula is unnecessarily complex. Doesn't this do what you want?

=IF(C11=B11, "24:00", "00:00")

[Yorkie580]

Thank you both - the ISBLANK seems to have done the trick.

[Yorkie580]

Sorry MarkRae - I should have explained further... the formula was to calculate the length of shift worked. occasionally people will start work at 1400 and finish at 0200. On some occasions they have worked 24hrs. The issue I had was in calculating the number of hours - if the start time was (in figures) later than the end time, or if the start and end time matched, the time worked was shown as either negative or zero. Expenses/allowances were calculated directly from the hours worked, so I needed to be able to cater for all eventualities.

[MarkRae]

Oh right. In that case, you could have saved yourself a huge amount of work by including the date as well as the time in the cell. Of course, you could have "hidden" the date portion by cell formatting, but including it would have meant that Excel would have done all of the work for you.

E.g. if the shift started at 09 Jul 2010 20:00 and finished at 10 Jul 2010 02:00, the length of shift would have been simple subtraction...