calculating no working days

AI Training: For Busy Decision Makers & Professionals Book now

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Calculating No. of Working Days Open, if two cells are blank, bu

Calculating No. of Working Days Open, if two cells are blank, bu

resolvedResolved · Medium Priority · Version 365

Alison has attended:
No courses

Calculating No. of Working Days Open, if two cells are blank, bu

I have a worksheet which calculates the date a response is due to a request based on the received date and with Todays Date and list of UK bank holidays in the background. Another column keeps a running count of the number of working days a request is open, until the response date is filled.

What I cannot figure out is how to stop the count of number of working days open if I have to seek more information and complete another cell with a Y.

I need something like if two cells are blank then calculate number of working days, but if only one cell is blank then do not calculate.

Edited on Wed 29 Jul 2020, 13:54

RE: Calculating No. of Working Days Open, if two cells are blank

Hi Alison,

Thank you for your question in our forum. My name is Ron and I am one of the trainers at STL.

What I understand from your question is that you want to execute one action if the dates are both there and no action if one of the dates is missing.

In Excel we can use the IF function to do that. The If function tests a condition and if the condition is true it will do one thing and if it is false it will do something else

The syntax of the IF function is:
=IF(Condition, True Part, False Part)

Related to your challenge, it would have a setup similar to the below. You just need to translate it to the cells and formulas you use.:

=IF(And(Cell with request date="",Cell with End Date=""),"",Networkdays function goes here)


The AND function lets you test multiple conditions and will return true if all the conditions are true.
So in the above function the conditions tested are that the request date cell is empty and the Reply date is empty.
If that is true then you apply a blank cell ("") because both cells have no date. If those conditions are not true, then it will apply the networkdays() function.

If you would like to learn more about Conditional functions and other more advanced functions that can help you tackle more complex calculation challenges then STL has the perfect course for you and currently we do excellent virtual deliveries in Microsoft Teams that are very well received. Just click the link below to see an overview of the topics covered in our Intermediate Excel course and contact one of our guys at STL if you need more information.
https://www.stl-training.co.uk/microsoft/excel-intermediate-courses.php

https://www.stl-training.co.uk/microsoft/excel-advanced-courses.php

Once again thank you for your forum question

Kind regards

Ron Oldeboom
Learning and Development Consultant
STL Training

Will be marked as resolved in 5 days

Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.

Will be marked as resolved in 5 days

Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.

RE: Will be marked as resolved in 5 days

Hi Ron

I attended the excellent training by your colleague Marius last week on Excel Intermediate, but we had to complete our organisation's evaluation form. It was a brilliant session and Marius copied brilliantly with the building work being carried on by his neighbours.

Your response covers what I have already tried, and I have been trying to get the correct function for a while now. I suspect it is too much for Excel.

Really hoping my organisation arranges some more training soon

Thanks

RE: Will be marked as resolved in 5 days

Hi Alison,

I don't mind at all setting up a Teams meeting with you to have a look at your conundrum. I am available tomorrow morning after 11.

If that is something you would like to do, just reply to this message and I will invite you. Don't forget to leave your email address so I can send you an invite

Kind regards

Ron

RE: Will be marked as resolved in 5 days

Sorry, I am only just getting to see your message. Thanks for the offer, but it has been decided that we don't need this functionality at this time.

Best wishes

Alison

RE: Will be marked as resolved in 5 days

Thank you for the update Alison. If there is any need in the future, the offer stands!

Kind regards

Ron

Mon 10 Aug 2020: Automatically marked as resolved.

 

Training courses

 

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Change the Default Width of All Columns in Excel 2010

If you want to change the width of the columns in your Excel 2010 spreadsheet, making them either larger or smaller, here's how:

In the Cells group on the Home tab, click Format.

Hover over the section called Cell Size and a drop down list will appear, select Default Width from this list.

In the Standard Width dialog box, enter the size you want to set as the default width and click OK.

View all Excel hints and tips


Server loaded in 0.12 secs.