Using a logical test on a date is failing

[Guest]

We have a situation where a date is entered into a cell. If that date is
before a trigger date, billing will apply from the trigger date. If that
date is after the trigger date, then billing will apply from that date.

Example: H8 = 12/7/06
Trigger Date = 1/1/07

Formula is =if(H8<1/1/7,1/1/7,H8)

Since H8 in this example is 12/7/06 the trigger date of 1/7/07 should be
used but it fails the test and returns the actual contents of H8 which is
12/7/06 instead. Why?

The serial number of 12/7/06 39058 and the serial number of 1/1/07 is 39083.

In my muddled brain, 39058 is less than 39083 and therfore the formula
should return 1/1/7 but it does not. What am I missing here?

 

[revinfo]

We have a situation where a date is entered into a cell. If that date is
before a trigger date, billing will apply from the trigger date. If that
date is after the trigger date, then billing will apply from that date.

Try this instead:

=IF(H8<1/1/7,DATE(2007,1,1),H8)


Bruce

 

[Guest]

=IF(H8<"1/1/2007","1/1/2007",H8)

 

[Guest]

Thank you for your input but that returns the same result.

 

[Guest]

This works:

For some reason, entering hard dates into the formula did not work. You
have to use cell references that contain the actual dates. Don’t ask me… I
have no clue why this is so but you can see it for yourself below.

Date Admitted Trigger Date Billing Date
12/7/2006 1/1/2007 1/1/07
1/15/2007 1/1/2007 1/15/07
39058 39083 =IF(H8<I8,I8,H8)


If the admittance date is earlier than then trigger date, then we use the
trigger date for billing.

If the admittance date is later than the trigger date, then we use the
admittance date for billing.

(It has to work both ways to be effective.)

Thank you for your input. Now if you understand why this works and hard
dates did not… I am really curious.

*******************************************************

 

[Dave Peterson]

=if(H8<date(2007,1,1),date(2007,1,1),H8)
or
=max(h8,date(2007,1,1))

(either has to be formated as a date)

1/1/07 means 1 divided by 1 divided by 7--not a date.

 

[Guest]

Quotation marks turn the date into text strings. (note the formatting on the
left of the cell). Cannot do math on text but thank you very much for your
input. I do appreciate it.

 

[David Biddulph]

Try changing that to =IF(H8<--"1/1/2007",--"1/1/2007",H8)