dates quandry with <> and MID

[IanWells]

Hi All

Struggling a little here with a formula and hope you can help...

the formula below may self explain but please ask if it doesnt, i know its wrong but sure its the right lines....

=networkdays(if(D18>(mid(L1,2,12))),(E18<(mid(M1,2,12)))

Im trying to get the networkdays count between dates using L1 (but if D18 is greater use that date) and M1 (but if E18 is before use that date).

The MID must be used in L1 & M1

Thanks in anticipation.

Ian

 

[Richard Schollar]

Hi Ian

Based on what you say:

=NETWORKDAYS(MAX(D18,MID(L1,2,12)+0),MIN(E18,MID(M1,2,12)+0))

 

[IanWells]

Thanks Richard.

Thats just what i needed...

Excellent

Thanks again.

I need a countifs formula based on the dates quandry also, shall i create a new thread or is it ok to proceed on this one if its ok with you?

Ian

 

[Richard Schollar]

Fine to stick with this - what do you need to do?

 

[IanWells]

Right here goes.....

i am using this formula..

=COUNTIFS(Data!A:A,'Diary Report'!A3,Data!L:L,N$1,Data!L:L,O$1)

N1 and O1 contains the dates with < & > (eg <01/01/2011) preceeding each, Now lets say in A1 and B1 we have More dates without < & > (01/01/2011).

I need the above formula to incorporate the latest date between A1 and N1 and the oldest date between B1 and O1.

Hope i've explained ok.

cheers

Ian

 

[IanWells]

Maybe you could answer this question a little easier.

If i use the MAX and MIN to ascertain the greater and lower! Why when i use the COUNTIFS if formula it doesnt seem to recognise the MAX and MIN date shown?...

Ian

 

[Richard Schollar]

You need to structure it like in the following (note actual dates are contained in the cells A1 and N1 and B1 and O1 (not a string like ">20/8/2011"):

=COUNTIFS(Data!A:A,'Diary Report'!A3,Data!L:L,"<" & MAX(N$1,$A$1),Data!L:L,">" & MIN(O$1,$B$1))

 

[IanWells]

Richard

Thanks very much for your time, i've achieved my goal a slightly different way now but your guidence was a big big help.

I used the MAX (I10) and MIN (J10) to get the dates i needed then used

=COUNTIFS(Data!A:A,'Diary Report'!A10,Data!L:L,">"&I10,Data!L:L,"<"&J10)

Thanks again, another fantastic help for me from mrexcel.com

best regards

Ian