Kay has attended:
Excel Advanced course
Excel Advanced - Formulas & Functions course
NETWORK Calculation
Hi All,
Please can you help to point me to the right direction for the following;
Column A3 = Start Date
(IF(ISBLANK(A3),"N/A",TODAY()-A3)),"N/A")
Instead of TODAY()-A3), I would like to return a result for number of working days between the start date and today.
Is it possible to amend the above calculation to return working days?
Many Thanks
RE: NETWORK Calculation
Hi Kay,
Thank you for the forum question.
=NETWORKDAYS(start_date, end_date, [holidays])
The Networkdays function counts 5 days a week Mon to Fri and also count bank holidays as working days, so you will need to have a list with the dates of the bank holidays (the [holidays] argument in the function), if you do not want bank holidays counted as working days.
=IF(ISBLANK(A3),"N/A",NETWORKDAYS(A3,TODAY())-1)
It is not a good idea to have the =TODAY() repeated many times. This can make the file very slow. A better solution is to have the function in a cell and then reference the cell in the NETWORKDAYS function.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
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
RE: NETWORK Calculation
Hi Jen,
Great to hear back from you, thanks so much.
Please can I check the reason for having to add -1 at the end of the suggested formula?
thanks
RE: NETWORK Calculation
Hi Kay,
The reason for -1. I assumed based on your approach that this was what you want. If the start date is today 20/06/2023 and you have the calculation 20/06/2023 minus =TODAY() the result will return 0 days, but the NETWORKDAYS function =NETWORKDAYS(20/06/2023,TODAY()) will return 1.
I hope this makes sense.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
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
