Excel Formula Guidance. Formula need to determine if cell is popul

[Matt]

Hi,

Hopefully someone will be able to provide some guidance.
I have been tasked with setting up some spreadsheets at work to application
coming into my department and the number of days it takes for us to deal with
them. To work this out is easy enough using formula
=NETWORKDAYS(B4,E4,$A$119:$A$158) with the numbers involved with the $
referencing bank holidays listed in the spreadsheet and B4 received date and
E4 being the completed date.

I now need to include some extra lines as I need to be able to monitor
written enquiries as well so the headings would read Application Received
(AR), Enquiry Received (ER), Completed Date (CD) so that you put the date in
either of the first two cells depending on which one needs to be completed.
So I now need a new formula that would look at AR determine if a date is in
the cell if it isn’t populated it looks at ER instead whichever cell is
completed it then works out the networkdays by looking at CD. I guess using
the same formula listed above.

Can this be done, as I have completed everything else and this is just the
one thing that is stumping me. If so can someone tell me what the formula
would say.

I have surprised myself that I was able to do everything else with a lot of
hard work and Google searches, this is just the one thing that is stumping me.

I hope I’ve explained myself well, which will enable someone to advice.

Thanking you in advance
Matt

 

[Lars-Åke Aspelin]

Hi,

Hopefully someone will be able to provide some guidance.
I have been tasked with setting up some spreadsheets at work to application
coming into my department and the number of days it takes for us to deal with
them. To work this out is easy enough using formula
=NETWORKDAYS(B4,E4,$A$119:$A$158) with the numbers involved with the $
referencing bank holidays listed in the spreadsheet and B4 received date and
E4 being the completed date.

I now need to include some extra lines as I need to be able to monitor
written enquiries as well so the headings would read Application Received
(AR), Enquiry Received (ER), Completed Date (CD) so that you put the date in
either of the first two cells depending on which one needs to be completed.
So I now need a new formula that would look at AR determine if a date is in
the cell if it isn’t populated it looks at ER instead whichever cell is
completed it then works out the networkdays by looking at CD. I guess using
the same formula listed above.

Can this be done, as I have completed everything else and this is just the
one thing that is stumping me. If so can someone tell me what the formula
would say.

I have surprised myself that I was able to do everything else with a lot of
hard work and Google searches, this is just the one thing that is stumping me.

I hope I’ve explained myself well, which will enable someone to advice.

Thanking you in advance
Matt

If AR is in column B and ER is in column C, try to replace B4 with
IF(B4<>"",B4,C4), like this:

=NETWORKDAYS(IF(B4<>"",B4,C4),E4,$A$119:$A$158)

Hope this helps / Lars-Åke

 

[Matt]

If AR is in column B and ER is in column C, try to replace B4 with
IF(B4<>"",B4,C4), like this:

=NETWORKDAYS(IF(B4<>"",B4,C4),E4,$A$119:$A$158)

Hope this helps / Lars-Ã…ke
.

That works a treat!! Many thanks

 

[Matt]

Hi,

Hopefully someone will be able to provide some guidance.
I have been tasked with setting up some spreadsheets at work to application
coming into my department and the number of days it takes for us to deal with
them. To work this out is easy enough using formula
=NETWORKDAYS(B4,E4,$A$119:$A$158) with the numbers involved with the $
referencing bank holidays listed in the spreadsheet and B4 received date and
E4 being the completed date.

I now need to include some extra lines as I need to be able to monitor
written enquiries as well so the headings would read Application Received
(AR), Enquiry Received (ER), Completed Date (CD) so that you put the date in
either of the first two cells depending on which one needs to be completed.
So I now need a new formula that would look at AR determine if a date is in
the cell if it isn’t populated it looks at ER instead whichever cell is
completed it then works out the networkdays by looking at CD. I guess using
the same formula listed above.

Can this be done, as I have completed everything else and this is just the
one thing that is stumping me. If so can someone tell me what the formula
would say.

I have surprised myself that I was able to do everything else with a lot of
hard work and Google searches, this is just the one thing that is stumping me.

I hope I’ve explained myself well, which will enable someone to advice.

Thanking you in advance
Matt

Just a thought.. would it be possible to have anything in place that does
that formula (which works fantastic BTW) but depending on which cell was used
(AR or ER) it would provide the answer in a different coloured font? For
example it works out which date it has to use for doing the networkdays and
if its used the date in AR it remains black and if it uses the date in ER it
turns red.

It just a thought as it that would then enable me to tell the difference
between which dates are being used by having a quick glance.

Again thanking you in advance.
Matt