Network Days & IF Statements

[bloodmilksky]

[h=2]Network Days & IF Statements[/h]

Hi Guys,

I hope you are all having a great friday

I was just wondering if anyone could help me combine the power of Networkdays and IF Statement so currently

I have a start Date In A1 and End Date in B1 then either AM,PM Or Full in C1 with the days total in D1

I wondered if anyone would know how to merge NetworkDays & IF together so if there was AM Or PM in C1 it would only count as 0.5 and if Full it would count as 1.

any help would be greatly appreciated

Jamie​

 

[Joe4]

Cross-Posted here: Network Days & IF Statements

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Declamatory]

You could try =IF(OR(C1="AM",C1="PM"),NETWORKDAYS(A1,B1)/2,NETWORKDAYS(A1,B1))

 

[bloodmilksky]

Thank you Declamatory but the sum is still only showing as 1 even with AM Or PM

 

[Declamatory]

Thank you Declamatory but the sum is still only showing as 1 even with AM Or PM

That's off. It worked for me. Have you formatted the cells to allow a decimal place otherwise it will round up to one.

 

[bloodmilksky]

I think so as it is showing as 1.00 and not just 1

 

[Declamatory]

OK, I've just tried it again.

Cell A1=31/03/2016
Cell B1=01/04/2016
Cell C1=AM

Cell D1 has the formula =IF(OR(C1="AM",C1="PM"),NETWORKDAYS(A1,B1)/2,NETWORKDAYS(A1,B1))

Cell D1 value = 1.0


Cell A1=31/03/2016
Cell B1=31/03/2016
Cell C1=AM

Cell D1 has the formula =IF(OR(C1="AM",C1="PM"),NETWORKDAYS(A1,B1)/2,NETWORKDAYS(A1,B1))

Cell D1 value = 0.5

 

[Jonmo1]

edit..
Thought post #7 was from OP at the time I wrote this response...

Those ARE the correct answers given the sample values you just posted.

There are 2 networkdays between 31/03/2016 and 04/01/2016
The networkdays count is INCLUSIVE of BOTH beginnning and ending dates, so 31/03/2016 counts as 1 day, 04/01/2016 counts as another, total of 2.
2/2 = 1

Try using the Formula Evaluation tool on the Formulas Tab, this will show the calucation steps in action.

 

[bloodmilksky]

Hi,

it was my fault there was a blank value in "am " and "pm " that I didnt see. the formula works perfectly.

thank you really appreciate your help