Help calculating dates within a forumula
 

I am trying to do a forumula that basicalls says that if a cell has a date
that is before 10/31/2007 than use the date in the cell minus 7 days. If the
date in the cell is great than 10/31/2007 than simply use 10/31/2007. The
problem is that when I enter 1/31/2007 in the forumula it simply divides the
numbers rather than reconizing it as a date. I have pasted the forumula here
to see if anyone has a solution to the problem. What ends up happening is
the cell just lists 1/1/1900.

=IF(E3(10/31/2007),(10/31/2007), IF(E3(10/31/2007),E3-7))


Thanks,
James

Here is the forumula:
--
James A. Resnick

Help calculating dates within a forumula
 

=IF(E3--"2007-10-31,--"2007-10-31", ,E3-7)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"James A. Resnick" wrote in message
...
I am trying to do a forumula that basicalls says that if a cell has a date
that is before 10/31/2007 than use the date in the cell minus 7 days. If
the
date in the cell is great than 10/31/2007 than simply use 10/31/2007. The
problem is that when I enter 1/31/2007 in the forumula it simply divides
the
numbers rather than reconizing it as a date. I have pasted the forumula
here
to see if anyone has a solution to the problem. What ends up happening is
the cell just lists 1/1/1900.

=IF(E3(10/31/2007),(10/31/2007), IF(E3(10/31/2007),E3-7))


Thanks,
James

Here is the forumula:
--
James A. Resnick

Help calculating dates within a forumula
 

Thanks Bob for your help, but it did not seem to work. What does the --
function mean?? I should note that the E3 field has a pre-populated date.

Any other ideas??
--
James A. Resnick


"Bob Phillips" wrote:

=IF(E3--"2007-10-31,--"2007-10-31", ,E3-7)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"James A. Resnick" wrote in message
...
I am trying to do a forumula that basicalls says that if a cell has a date
that is before 10/31/2007 than use the date in the cell minus 7 days. If
the
date in the cell is great than 10/31/2007 than simply use 10/31/2007. The
problem is that when I enter 1/31/2007 in the forumula it simply divides
the
numbers rather than reconizing it as a date. I have pasted the forumula
here
to see if anyone has a solution to the problem. What ends up happening is
the cell just lists 1/1/1900.

=IF(E3(10/31/2007),(10/31/2007), IF(E3(10/31/2007),E3-7))


Thanks,
James

Here is the forumula:
--
James A. Resnick

Help calculating dates within a forumula
 

What Bob meant to write is
=IF(E3--"2007-10-31",--"2007-10-31", E3-7)



"Bob Phillips" wrote:

=IF(E3--"2007-10-31,--"2007-10-31", ,E3-7)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"James A. Resnick" wrote in message
...
I am trying to do a forumula that basicalls says that if a cell has a date
that is before 10/31/2007 than use the date in the cell minus 7 days. If
the
date in the cell is great than 10/31/2007 than simply use 10/31/2007. The
problem is that when I enter 1/31/2007 in the forumula it simply divides
the
numbers rather than reconizing it as a date. I have pasted the forumula
here
to see if anyone has a solution to the problem. What ends up happening is
the cell just lists 1/1/1900.

=IF(E3(10/31/2007),(10/31/2007), IF(E3(10/31/2007),E3-7))


Thanks,
James

Here is the forumula:
--
James A. Resnick

Help calculating dates within a forumula
 

WOW it worked...THANKS

So my question is, what does -- mean?? I just want to understand the
formula on a conceptual level.
--
James A. Resnick


"bau" wrote:

What Bob meant to write is
=IF(E3--"2007-10-31",--"2007-10-31", E3-7)



"Bob Phillips" wrote:

=IF(E3--"2007-10-31,--"2007-10-31", ,E3-7)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"James A. Resnick" wrote in message
...
I am trying to do a forumula that basicalls says that if a cell has a date
that is before 10/31/2007 than use the date in the cell minus 7 days. If
the
date in the cell is great than 10/31/2007 than simply use 10/31/2007. The
problem is that when I enter 1/31/2007 in the forumula it simply divides
the
numbers rather than reconizing it as a date. I have pasted the forumula
here
to see if anyone has a solution to the problem. What ends up happening is
the cell just lists 1/1/1900.

=IF(E3(10/31/2007),(10/31/2007), IF(E3(10/31/2007),E3-7))


Thanks,
James

Here is the forumula:
--
James A. Resnick

Help calculating dates within a forumula
 

On Wed, 15 Aug 2007 07:44:02 -0700, James A. Resnick
wrote:

I am trying to do a forumula that basicalls says that if a cell has a date
that is before 10/31/2007 than use the date in the cell minus 7 days. If the
date in the cell is great than 10/31/2007 than simply use 10/31/2007. The
problem is that when I enter 1/31/2007 in the forumula it simply divides the
numbers rather than reconizing it as a date. I have pasted the forumula here
to see if anyone has a solution to the problem. What ends up happening is
the cell just lists 1/1/1900.

=IF(E3(10/31/2007),(10/31/2007), IF(E3(10/31/2007),E3-7))


Thanks,
James

Here is the forumula:


=IF(E3("10/31/2007"),("10/31/2007"), IF(E3<("10/31/2007"),E3-7))

or

=IF(E3(DATE(2007,10,31)),(DATE(2007,10,31)), IF(E3<(DATE(2007,10,31)),E3-7))


--ron

Help calculating dates within a forumula
 

James, I want the answer, too.
Waiting for Bob or someone else ...

"James A. Resnick" wrote:

WOW it worked...THANKS

So my question is, what does -- mean?? I just want to understand the
formula on a conceptual level.
--
James A. Resnick


"bau" wrote:

What Bob meant to write is
=IF(E3--"2007-10-31",--"2007-10-31", E3-7)



"Bob Phillips" wrote:

=IF(E3--"2007-10-31,--"2007-10-31", ,E3-7)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"James A. Resnick" wrote in message
...
I am trying to do a forumula that basicalls says that if a cell has a date
that is before 10/31/2007 than use the date in the cell minus 7 days. If
the
date in the cell is great than 10/31/2007 than simply use 10/31/2007. The
problem is that when I enter 1/31/2007 in the forumula it simply divides
the
numbers rather than reconizing it as a date. I have pasted the forumula
here
to see if anyone has a solution to the problem. What ends up happening is
the cell just lists 1/1/1900.

=IF(E3(10/31/2007),(10/31/2007), IF(E3(10/31/2007),E3-7))


Thanks,
James

Here is the forumula:
--
James A. Resnick

Help calculating dates within a forumula
 

It is used to coerce a value into a number. In this case, "2007-10-31" is a
string representing a date, not an actual date. But by preceding it with --,
Excel will coerce that string into the value 39386, which is the underlying
value of 31st Oct 2007. A single - will coerce it to the number, but negates
it as well, so another - is used to negate it back. This enables you to use
a familiar date form in the formula, without putting the actual number. You
could also use DATE(2007,10,31), I just prefer the former as it reads better
to me.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"bau" wrote in message
...
James, I want the answer, too.
Waiting for Bob or someone else ...

"James A. Resnick" wrote:

WOW it worked...THANKS

So my question is, what does -- mean?? I just want to understand the
formula on a conceptual level.
--
James A. Resnick


"bau" wrote:

What Bob meant to write is
=IF(E3--"2007-10-31",--"2007-10-31", E3-7)



"Bob Phillips" wrote:

=IF(E3--"2007-10-31,--"2007-10-31", ,E3-7)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"James A. Resnick" wrote in message
...
I am trying to do a forumula that basicalls says that if a cell has
a date
that is before 10/31/2007 than use the date in the cell minus 7
days. If
the
date in the cell is great than 10/31/2007 than simply use
10/31/2007. The
problem is that when I enter 1/31/2007 in the forumula it simply
divides
the
numbers rather than reconizing it as a date. I have pasted the
forumula
here
to see if anyone has a solution to the problem. What ends up
happening is
the cell just lists 1/1/1900.

=IF(E3(10/31/2007),(10/31/2007), IF(E3(10/31/2007),E3-7))


Thanks,
James

Here is the forumula:
--
James A. Resnick

Help calculating dates within a forumula
 

Thank you much, Bob!

"Bob Phillips" wrote:

It is used to coerce a value into a number. In this case, "2007-10-31" is a
string representing a date, not an actual date. But by preceding it with --,
Excel will coerce that string into the value 39386, which is the underlying
value of 31st Oct 2007. A single - will coerce it to the number, but negates
it as well, so another - is used to negate it back. This enables you to use
a familiar date form in the formula, without putting the actual number. You
could also use DATE(2007,10,31), I just prefer the former as it reads better
to me.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"bau" wrote in message
...
James, I want the answer, too.
Waiting for Bob or someone else ...

"James A. Resnick" wrote:

WOW it worked...THANKS

So my question is, what does -- mean?? I just want to understand the
formula on a conceptual level.
--
James A. Resnick


"bau" wrote:

What Bob meant to write is
=IF(E3--"2007-10-31",--"2007-10-31", E3-7)



"Bob Phillips" wrote:

=IF(E3--"2007-10-31,--"2007-10-31", ,E3-7)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"James A. Resnick" wrote in message
...
I am trying to do a forumula that basicalls says that if a cell has
a date
that is before 10/31/2007 than use the date in the cell minus 7
days. If
the
date in the cell is great than 10/31/2007 than simply use
10/31/2007. The
problem is that when I enter 1/31/2007 in the forumula it simply
divides
the
numbers rather than reconizing it as a date. I have pasted the
forumula
here
to see if anyone has a solution to the problem. What ends up
happening is
the cell just lists 1/1/1900.

=IF(E3(10/31/2007),(10/31/2007), IF(E3(10/31/2007),E3-7))


Thanks,
James

Here is the forumula:
--
James A. Resnick