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

littlered136

New Member
Joined
Oct 13, 2018
Messages
5
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Maybe this...

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

Note: I reduced 46800/86400 to is lowest terms.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top