MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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?


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


The formula is:


Assuming that you have in A2


in B2


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.


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.