how to make a formula not calculate/display until all parts of the cell range are complete

[littlered136]

Hi

I am trying to adjust an excel timesheet given to me. I have got a formula to calculate the broken turnaround, but I don't want it to calculate/display until two of the cells within the formula are filled in.

Otherwise it auto displays a negative number till they are filled in.

The formula I have is =SUM(D22 - C24 - 46800/86400) (where D22 is the time I leave say on a Monday night and C24 is the time I am called the next day say on Tuesday morning). The formula is calculating how many hours I break the 11 hour turn around.

I think it is the IF command but I have made several attempts to try to make it work but am completely stumped as it will not calculate and says I got the formula wrong.

Any help would be greatly appreciated.

Thanks

 

[Rick Rothstein]

Maybe this...

=IF(OR(D22="",C24=""),"",SUM(D22-C24-13/24))

Note: I reduced 46800/86400 to is lowest terms.

 

[littlered136]

Hi Thank you, this worked wonderfully. I have another question about the formula.

I also am trying to make it round up to the next hour, so if I break turnaround by 1hr 20 mins, it rounds it up to 2 hours. I currently have it in the cell below taking the result of the calculation you just gave me above as the cell range and using this formula =CEILING(N18,1/24)

is there a way to make the original formula round it up, cos every time I try it says my formula is wrong. am I trying to do something that is not possible and that is why it is going wrong.

thanks

 

[Rick Rothstein]

See if this works for you...

=IF(OR(D22="",C24=""),"",CEILING(SUM(D22-C24-13/24),1/24))

 

[littlered136]

Yes this works. Than you for your help. I see now where I was going wrong. Thank you for helping me learn two new things in excel.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...till-all-cells-are-filled-in.html#post4991348
@littlered136
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[littlered136]

sorry I was doing this on my iPhone and didn't see that rule, thank you for letting me know.

 

[littlered136]

Hi I was also wondering how do I make it so it doesn't display if it is a negative number?

Thanks