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?

On Mar 22, 10:44 am, Les Thompson <Les
wrote:
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

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?

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

--
Gary''s Student
gsnu200711

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

" wrote:

On Mar 22, 10:44 am, Les Thompson <Les
wrote:
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

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?

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. Dont 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.

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

" wrote:

On Mar 22, 10:44 am, Les Thompson <Les
wrote:
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

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?

=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.

Les Thompson wrote:

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?

--

Dave Peterson

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.

"Gary''s Student" wrote:

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

--
Gary''s Student
gsnu200711

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

"Les Thompson" wrote in message
...
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.

"Gary''s Student" wrote:

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

--
Gary''s Student
gsnu200711