Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
I need help calculating dates? | Excel Worksheet Functions | |||
calculating dates | Excel Worksheet Functions | |||
calculating with dates | Excel Discussion (Misc queries) | |||
calculating with dates | Excel Worksheet Functions |