Posted by gavin on May 09, 2001 8:44 AM

Yesterday you gave me a formula for calculating the number of days between two dates, with a time string included in the calculation.

I have tried this and it reject the part with the time string in it by highlighting 1500:

c&LT;1500 any ideas?

Gavin

Posted by Aladin Akyurek on May 09, 2001 9:36 AM

Gavin,

The formula is:

=IF(ISNUMBER(A2),IF(C2&LT;"15:00",NETWORKDAYS(A2,B2),""),"")

Assuming that you have in A2

04-05-2001,

in B2

06-05-2001

and in C2

14:45 [ the format of C2 is important ]

you get 1 as result.

If you use military time in C2 in the form 1445, then

the IF(C2&LT;"15:00" part should be changed to: IF(C2&LT;1500.

I believe I used the latter form in my first response. Plus a few mistakes like an extraneous paren and no 3rd argument for the first IF.
I was converting the formula from the semi-English semi-Dutch system that we use (sigh) at school. Just like those semicolons that I forget to convert to commas. My apologies.

Posted by Mark W. on May 09, 2001 5:41 PM

Date Coercion

Aladin/Gavin, if C2 contains a date value formatted
as hh:mm or hhmm, then the text representation of
that date value (e.g., "15:00") must be coerced
into a date value before a comparison. If you
enter 15:00 into C2 the formula, =C2="15:00", will
return FALSE. Only after "15:00" has been coerced
into a date value by adding zero (i.e., =C2="15:00"+0)
will it return TRUE. So your conditional in the