• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Networkdays Formula

Baz

New Member
Hi, I've built a spreadsheet that looks at the start and end dates of someone being in a role e.g. 08/01/2018 to 20/12/2019. I've put the end of every month along the top column (31/01/19, 28/02/19, etc.) to help drive the following formulas =IF(AND($G$2>=$B9,$G$2<=$C9),"Yes","No"). However if I've got an end date that is before the end of the month how do I manipulate this formula to make sure that I get the correct answer because at the moment I'm having to manually and go in to amend it?
 
Thankyou for the hint Vietm, much appreciated.
 

Attachments

  • Chandoo Excel Question regarding an if function.xlsx
    51.8 KB · Views: 2
Baz
hmm ... hmm ... I missed something ... something You should add ...
'Working Days 2019' ... You should add there days which 'can stay out of work'
>> those would effect to values R1:AD1 (Forecast-sheet)
>> >> means too that I deleted those few columns!
I didn't get Your end before end ... challenge
>>>>> I need to get clear samples 'what do You mean?'
... but ...
>> I changed those dates ... C:R are 1st days and S:AD as last days.
>> >> Now, You could compare those as needs
Your 'Yes'/'No' ...
>> You need to see ONLY 'Yes'! If both - it's a challenge to notice if 'No'...

Cell R36 ... seem to be something ... not only pure date.
(( I even didn't to modify 'everything' ... ))
 
Baz
... maybe I found solution for You challenge ...
I gotta modify 'more' ...
You could see monthly values which depends of 'working days' (after You've named those 'non-workingdays').
 

Attachments

  • Chandoo Excel Question regarding an if function.xlsx
    52.4 KB · Views: 10
Hi Vietm,
Thankyou for updating my spreadsheet but the formulas in columns G to R don't always work. For example if you look at row 36 for Paul Natali his start date was 28/01/19 but instead of the formula to bring back "Yes" it's showing as -, which is incorrect as the date is within January 2019. Can you or someone else help me with this please?
 
Back
Top