98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » IF ISBLANK and NetworkDays combined
IF ISBLANK and NetworkDays combined
Resolved · High Priority · Version 2010
Mon 18 Jul 2016, 12:56Delegate Andrew said...
Andrew has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Excel Advanced - For Power Users course
Excel Dashboards for Business Intelligence course
IF ISBLANK and NetworkDays combined
I have a formula that tells me how many days have elapsed between start and end date. If the end is blank then the formula uses ISBLANK to keep the cell empty.
How do incorporate NETWORKDAYS into this formula? I think I'm misplacing the brackets?
H10 is the end date & A10 the start date
=IF(ISBLANK(H10),"",H10-A10)
Mon 18 Jul 2016, 13:19Trainer Jens said...
RE: IF ISBLANK and NetworkDays combined
Hi Andrew,
Thank you for the forum question.
Try like this:
=IF(ISBLANK(H10),"",NETWORKDAYS(A10,H10))
If you want to exclude bank holidays you will need a list and let us just say that you have the list in the range k1:K10 then the formula should look like this:
=IF(ISBLANK(H10),"",NETWORKDAYS(A10,H10,K1:K10))
I hope this can help you.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Mon 18 Jul 2016, 13:30Delegate Andrew said...
RE: IF ISBLANK and NetworkDays combined
Thanks Jens but I've tried that and keep getting a #NAME? error
=IF(ISBLANK(H10),"",(NETWORKDAYS,A10,H10))
H10 = End date which can be blank
A10 will always be populated with a start date
Mon 18 Jul 2016, 13:43Trainer Jens said...
RE: IF ISBLANK and NetworkDays combined
Hi Andrew,
I have tested the functions in my workbook and they worked fine.
The problem can be the formatting. Please check that the cells are date formatted and also if the H10 is blank (Is it blank because another function makes it blank). There is a different being blank or Empty.
If you want I can have a look at the workbook.
You can send it to:
info@stl-training.co.uk
If you send it please add my name in the subject.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Mon 18 Jul 2016, 13:35Delegate Andrew said...
RE: IF ISBLANK and NetworkDays combined
Hi Jens,
I added an extra bracket by mistake - your solution works!
Many thanks
Andrew
Mon 18 Jul 2016, 13:44Trainer Jens said...
RE: IF ISBLANK and NetworkDays combined
Hi Andrew,
I have got the message that it is working.
Good.
Please let me know if we can help you another time.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Page Break Preview in Excel 2010 (Hint/tip)If you select View then Workbook Views then Page Break Preview, you will be able to view how your Excel spreadsheet will be split across multiple pages when printed. Even better, you can also drag a page break to a new place. Excel will then scale down your entire worksheet to fit the information you want on the pages you want. |
