calculating time in 24hr clock with added conditions

[nje]

Hello
I have made a complicated formula (to me it's complicated anyway) made up from various formulas I have got from very helpful people on this site. I'm struggling with it now so I would be grateful for any help. (Worksheet attached)
The worksheet is a pilots logbook, and column 'G' dictates which column the hours are logged e.g. If 'P1' or 'PIC' is entered into Column 'G' the the hours are recorded into Column 'L'
If 'PUT' or 'P1/s' is in column 'G' the the time is put into column 'M'.
That all works OK but if the flight is over-night for instance starts at 23:50 and ends at 01:50 all I get is ######## (please see worksheet). I have tried by coming up with this formula but it doesn't work and I'm sure someone will spot why. I apologise in advance, but I have tried to sort it out.

=IF(OR(G7="P1",G7="PIC",)*J7="","",-(J7>K7)+K7-J7)

If possible I woul also like to use the 24 hour clock by not having to put in the colons, I have read an interesting post on here where it advises to use 00\:00 as a custom format. This does work but getting it to add up is another problem

Thanks very much, I hope you can help

[zbor]

I must admit I don't quite understand but maybe this in L7:

=IF(J7="", "", IF(OR(G7="P1",G7="PIC"), MOD(K7-J7,1)), "")

[nje]

Thanks ZBOR, is it my question you don't understand? I've tried your fomula but I can't get it twork but will keep trying in case I hav ecopied it down wrong. Thaks again

[daddylonglegs]

I think Zbor's suggestion should be like this

=IF(J7="","",IF(OR(G7="P1",G7="PIC"),MOD(K7-J7,1),""))

That assumes you have times in J7 and K7. If you want to type in just 2350 and 0700 without the colons then format as you suggested and use this version

=IF(J7="","",IF(OR(G7="P1",G7="PIC"),MOD(TEXT(K7,"00\:00")-TEXT(J7,"00\:00"),1),""))

to input the times you can omit leading zeroes so for 07:00 AM you can just input 700

[nje]

Daddylonglegs - Thank you very much. It all works, I'm goig to use the the 24 hour clock without the colons as it seems to perform perfectly. I really appreciate your help. Can you foresee any problems using the the 24 hour clock this way, all I ever want it to do is just add to up, it isn't restricted to a certain number is it? as it might eventually have to count over a 1000 hours or more. I have checked your formula against ZBOR and the only difference that I could see was a space between the comma and the word MOD

[daddylonglegs]

What will be over 1000 hours? If you are summing columns L or M to get those sums then that won't be a problem - those result columns will still be in "real time" format.

[nje]

Daddylonglegs - Thanks, the columns to be added are L & M and it would be those (including the grand total) that may go up to or over a 1000.