Formula issues getting blank rather than zero

[Detectiveclem]

What am I doing wrong

I am using this formula in cell E7 successfully =IF(D7-C7<0, 1+D7-C7, D7-C7).

But when cell C7 has no data I do NOT want ‘0’ to appear in E7. I have tried various formulas including this one =IF(C7=””,””,D7-C7<0, 1+D7-C7, D7-C7) but it doesn’t work.

Can someone please help me rectify the formula so that cell E7 remains blank until data is entered in C7.

Thank you

 

[Peter_SSs]

i think fluff has it

Possibly, but I'm not so sure. Suppose C7 and D7 were both 4 and F7 & I7 were blank. Then E7 formula would give 0, H7 & K7 would be ""
In that case I suspect that the legitimate answer of adding E7, H7 and K7 is 0 whereas Fluff's formula would return "".

Therefore, my thought is that this is what may be needed.

=IF(LEN(E7&H7&K7),SUM(E7,H7,K7),"")

 

[Detectiveclem]

Hi Fluff & Squidd, This works thank you, but now causes an issue with my FINAL formula

=INT(L13)*24+HOUR(L13)+ROUND(MINUTE(L13)/60,2) which works fine if there is data in cell I use your formula in, but when it is blank I get #VALUE ! Can you further help me with this formula?

Thanks again Paul

 

[Fluff]

Try
=if(L13="","",INT(L13)*24+HOUR(L13)+ROUND(MINUTE(L13)/60,2))

 

[Peter_SSs]

Hi Fluff & Squidd, This works thank you,

Are you saying that the values in columns C & D in a row can never be equal? .. and the same for columns F & G and I & J?

 

[Detectiveclem]

Hi Peter, yes C is the start time D is the end time (session 1) F is the start time of Session 2 and G is the end time. The exact same with I and J with the total then being shown in L (In the Time Format) M then converts this to Decimal O is for inputting what the individual is claiming for payment P then shows the residual amount of time which is then added to Q which is a running total of hours and minutes accumulated to allow Flexi time off in the future. Therefore if no hours are recorded but 8 hours are taken off the individual simply shows how many hours they are claiming for payment which then deducts this from their 'bank hours'. Fluff's formula appears to have done the trick. Thanks again, I genuinely appreciate everyones help. Paul

 

[Detectiveclem]

Hi Fluff, Perfect thank you. Sorry for the delay in getting back to you but had to pop out. Just tried it and everything is great now. Really appreciate your help. Paul

 

[Fluff]

You're welcome & thanks for the feedback

 

[Detectiveclem]

Sorry, one last issue I have discovered. For example when I use the following formula at P23

=IF(M23="","",M23-O23)

everything is fine so long as M23 has data in the cell.

M23 contains the total hours worked by that person on a particular date.

However, if that person decides not to work that day, which means that M23 will remain blank as I want it to.

But if they record that they have take time off (using their banked time) in O23, I want P23 to show a minus figure. E.G. If they show 8 (hours) off in O23 I want P23 to show -8 rather than staying blank.

I again would be very grateful for your help.

Thanks Paul

 

[Fluff]

How about
=IF(AND(M23="",P23=""),"",IF(M23="",-P23,M23-O23))

 

[Detectiveclem]

Hi Fluff, it returned a zero in P23, not a minus figure I was hoping for as I had placed 8 in O23.

Excel displayed the following message,

"There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly. Try removing or changing these references, or moving the formulae to different cells".

I am basically asking that Cell P23 remains blank unless there is data in either M23 or O23. If any number is entered in O23 when M23 is blank then in P23 the figure should be the the same as O23 but as a minus.

I hope this assists.

Thank you so much. Paul