message for aladin
Posted by gavin on May 09, 2001 8:44 AM
Hi Aladin,
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<1500 any ideas?
Gavin
Posted by Aladin Akyurek on May 09, 2001 9:36 AM
Gavin,
The formula is:
=IF(ISNUMBER(A2),IF(C2<"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<"15:00" part should be changed to: IF(C2<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.
Aladin
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
formula below should read: C2<"15:00"+0.
