##value error

Can someone please help me with an error message for the following formulas:
=IF(I2="",0,(DATEDIF(I2,TODAY(),"Y")>=3)+0)
=IF(I2="",0,(DATEDIF(I2,TODAY(),"Y")<=2)+0)
It has only come up on this one computer. It's fine on all others. Have
checked the computer's date based on a suggestion but the date is correct.
Can anyone please help?
Sue

Hi Sue

You would be better keeping your posting in the same thread.
That way, other people can see the answers that have been offered so far, an
prevent duplication of effort.

It sounds as though you have checked the other 2? computers (as per your
original posting) but one is till returning the error.
The date for today() is correct.
The values in I2 is definitely before today().
It is a 0 (zero) at the end of the formula, and not a capital "O"?

Your formula works absolutely fine for me, and I cannot think of other
possibilities, but others might. Hopefully the foregoing will bring others
up to speed on where we have got to thus far.

Regards

Roger Govier


Sue wrote:
> Can someone please help me with an error message for the following formulas:
> =IF(I2="",0,(DATEDIF(I2,TODAY(),"Y")>=3)+0)
> =IF(I2="",0,(DATEDIF(I2,TODAY(),"Y")<=2)+0)
> It has only come up on this one computer. It's fine on all others. Have
> checked the computer's date based on a suggestion but the date is correct.
> Can anyone please help?
> Sue

On Thu, 10 Nov 2005 11:56:10 -0800, Sue <[email protected]> wrote:

>Can someone please help me with an error message for the following formulas:
>=IF(I2="",0,(DATEDIF(I2,TODAY(),"Y")>=3)+0)
>=IF(I2="",0,(DATEDIF(I2,TODAY(),"Y")<=2)+0)
>It has only come up on this one computer. It's fine on all others. Have
>checked the computer's date based on a suggestion but the date is correct.
>Can anyone please help?
>Sue

Check the regional settings (Start/Control Panel/Regional and Language
settings) and be sure the date format for that setting is the same as the date
format that is being used for data ENTRY into I2.

Certain entries may look like perfectly normal dates, but if the computer
(Windows Control Panel --- NOT EXCEL) is set to a different format, it cannot
be interpreted as a date by Excel and you will get the VALUE error.

For example, 12/13/2004 is a perfectly valid date with US settings (mm/dd/yyyy)
but would be invalid with UK settings (dd/mm/yyyy) since there is no 13th
month.


--ron

Hi Roger,
My apologies. I presumed that as I had got no reply to my last post that I
had to start again with a new thread. Reasonably new at this. There is only
one computer that is not responding like it should as per my first posting.
The "two" is the no. of columns with the formula. I rang the people with the
problem PC and they confirmed that the date was for today based on your
suggestion.
Based on Ron's reply to this thread, I will need to travel out there and
take a visual look at what is going on.
Many thanks.
Sue

"Roger Govier" wrote:

> Hi Sue
>
> You would be better keeping your posting in the same thread.
> That way, other people can see the answers that have been offered so far, an
> prevent duplication of effort.
>
> It sounds as though you have checked the other 2? computers (as per your
> original posting) but one is till returning the error.
> The date for today() is correct.
> The values in I2 is definitely before today().
> It is a 0 (zero) at the end of the formula, and not a capital "O"?
>
> Your formula works absolutely fine for me, and I cannot think of other
> possibilities, but others might. Hopefully the foregoing will bring others
> up to speed on where we have got to thus far.
>
> Regards
>
> Roger Govier
>
>
> Sue wrote:
> > Can someone please help me with an error message for the following formulas:
> > =IF(I2="",0,(DATEDIF(I2,TODAY(),"Y")>=3)+0)
> > =IF(I2="",0,(DATEDIF(I2,TODAY(),"Y")<=2)+0)
> > It has only come up on this one computer. It's fine on all others. Have
> > checked the computer's date based on a suggestion but the date is correct.
> > Can anyone please help?
> > Sue
>

Hi Sue

In addition to Ron's suggestions, I was also thinking about Text entries.
Are the dates in I2 true dates, or text values looking like dates?
A simple test in a blank cell of =I2+1 would resolve this, and I guess could
be carried out over the phone rather than a visit - if it's not too late!!

Regards

Roger Govier


Sue wrote:
> Hi Roger,
> My apologies. I presumed that as I had got no reply to my last post that I
> had to start again with a new thread. Reasonably new at this. There is only
> one computer that is not responding like it should as per my first posting.
> The "two" is the no. of columns with the formula. I rang the people with the
> problem PC and they confirmed that the date was for today based on your
> suggestion.
> Based on Ron's reply to this thread, I will need to travel out there and
> take a visual look at what is going on.
> Many thanks.
> Sue
>
> "Roger Govier" wrote:
>
>
>>Hi Sue
>>
>>You would be better keeping your posting in the same thread.
>>That way, other people can see the answers that have been offered so far, an
>>prevent duplication of effort.
>>
>>It sounds as though you have checked the other 2? computers (as per your
>>original posting) but one is till returning the error.
>>The date for today() is correct.
>>The values in I2 is definitely before today().
>>It is a 0 (zero) at the end of the formula, and not a capital "O"?
>>
>>Your formula works absolutely fine for me, and I cannot think of other
>>possibilities, but others might. Hopefully the foregoing will bring others
>>up to speed on where we have got to thus far.
>>
>>Regards
>>
>>Roger Govier
>>
>>
>>Sue wrote:
>>
>>>Can someone please help me with an error message for the following formulas:
>>>=IF(I2="",0,(DATEDIF(I2,TODAY(),"Y")>=3)+0)
>>>=IF(I2="",0,(DATEDIF(I2,TODAY(),"Y")<=2)+0)
>>>It has only come up on this one computer. It's fine on all others. Have
>>>checked the computer's date based on a suggestion but the date is correct.
>>>Can anyone please help?
>>>Sue
>>

On Fri, 11 Nov 2005 12:39:52 +0000, Roger Govier
<[email protected]> wrote:

>Hi Sue
>
>In addition to Ron's suggestions, I was also thinking about Text entries.
>Are the dates in I2 true dates, or text values looking like dates?
>A simple test in a blank cell of =I2+1 would resolve this, and I guess could
>be carried out over the phone rather than a visit - if it's not too late!!
>
>Regards
>
>Roger Govier

Did you try that?

On my system (Excel 2002), text entries that are otherwise legal dates will get
handled correctly by Sue's formulas.


--ron

Hi Ron

Yes I did, and found(to my surprise) that it worked with just the single
quote in front of the date (XL2003).
However, I then also inserted a space, after the single quote, and it failed
so I thought it worthwhile suggesting that Sue tested the validity of her
date entries in I2.

Regards

Roger Govier


Ron Rosenfeld wrote:
> On Fri, 11 Nov 2005 12:39:52 +0000, Roger Govier
> <[email protected]> wrote:
>
>
>>Hi Sue
>>
>>In addition to Ron's suggestions, I was also thinking about Text entries.
>>Are the dates in I2 true dates, or text values looking like dates?
>>A simple test in a blank cell of =I2+1 would resolve this, and I guess could
>>be carried out over the phone rather than a visit - if it's not too late!!
>>
>>Regards
>>
>>Roger Govier
>
>
> Did you try that?
>
> On my system (Excel 2002), text entries that are otherwise legal dates will get
> handled correctly by Sue's formulas.
>
>
> --ron

Hi Roger,
Thanks for the ideas. Unfortunately the business is closed for the weekend
so will try to follow up on Monday afternoon. All good ideas from you and
Ron.
Thanks heaps
Sue

"Roger Govier" wrote:

> Hi Sue
>
> In addition to Ron's suggestions, I was also thinking about Text entries.
> Are the dates in I2 true dates, or text values looking like dates?
> A simple test in a blank cell of =I2+1 would resolve this, and I guess could
> be carried out over the phone rather than a visit - if it's not too late!!
>
> Regards
>
> Roger Govier
>
>
> Sue wrote:
> > Hi Roger,
> > My apologies. I presumed that as I had got no reply to my last post that I
> > had to start again with a new thread. Reasonably new at this. There is only
> > one computer that is not responding like it should as per my first posting.
> > The "two" is the no. of columns with the formula. I rang the people with the
> > problem PC and they confirmed that the date was for today based on your
> > suggestion.
> > Based on Ron's reply to this thread, I will need to travel out there and
> > take a visual look at what is going on.
> > Many thanks.
> > Sue
> >
> > "Roger Govier" wrote:
> >
> >
> >>Hi Sue
> >>
> >>You would be better keeping your posting in the same thread.
> >>That way, other people can see the answers that have been offered so far, an
> >>prevent duplication of effort.
> >>
> >>It sounds as though you have checked the other 2? computers (as per your
> >>original posting) but one is till returning the error.
> >>The date for today() is correct.
> >>The values in I2 is definitely before today().
> >>It is a 0 (zero) at the end of the formula, and not a capital "O"?
> >>
> >>Your formula works absolutely fine for me, and I cannot think of other
> >>possibilities, but others might. Hopefully the foregoing will bring others
> >>up to speed on where we have got to thus far.
> >>
> >>Regards
> >>
> >>Roger Govier
> >>
> >>
> >>Sue wrote:
> >>
> >>>Can someone please help me with an error message for the following formulas:
> >>>=IF(I2="",0,(DATEDIF(I2,TODAY(),"Y")>=3)+0)
> >>>=IF(I2="",0,(DATEDIF(I2,TODAY(),"Y")<=2)+0)
> >>>It has only come up on this one computer. It's fine on all others. Have
> >>>checked the computer's date based on a suggestion but the date is correct.
> >>>Can anyone please help?
> >>>Sue
> >>
>

Hi Ron,
Your suggestion is a good point as in NZ we use dd/mm/yy for date format and
the computers I think have a default on purchasing of US dates so it may well
be the answer that they may not have changed the setting to NZ.
Thanks for the help. Can't get it checked now until Monday afternoon as it
is now the weekend. Will let both you and Roger know how I go.
Thanks heaps
Sue

"Ron Rosenfeld" wrote:

> On Fri, 11 Nov 2005 12:39:52 +0000, Roger Govier
> <[email protected]> wrote:
>
> >Hi Sue
> >
> >In addition to Ron's suggestions, I was also thinking about Text entries.
> >Are the dates in I2 true dates, or text values looking like dates?
> >A simple test in a blank cell of =I2+1 would resolve this, and I guess could
> >be carried out over the phone rather than a visit - if it's not too late!!
> >
> >Regards
> >
> >Roger Govier
>
> Did you try that?
>
> On my system (Excel 2002), text entries that are otherwise legal dates will get
> handled correctly by Sue's formulas.
>
>
> --ron
>

Hi Ron,
Your answer was exactly right. I shot out there first thing yesterday
morning (Monday) and found their setting was for English (US) and it is now
running beautifully. Thanks for the help. Sometimes the most obvious things
are the last things I think of.
Many thanks
Sue

"Ron Rosenfeld" wrote:

> On Thu, 10 Nov 2005 11:56:10 -0800, Sue <[email protected]> wrote:
>
> >Can someone please help me with an error message for the following formulas:
> >=IF(I2="",0,(DATEDIF(I2,TODAY(),"Y")>=3)+0)
> >=IF(I2="",0,(DATEDIF(I2,TODAY(),"Y")<=2)+0)
> >It has only come up on this one computer. It's fine on all others. Have
> >checked the computer's date based on a suggestion but the date is correct.
> >Can anyone please help?
> >Sue
>
> Check the regional settings (Start/Control Panel/Regional and Language
> settings) and be sure the date format for that setting is the same as the date
> format that is being used for data ENTRY into I2.
>
> Certain entries may look like perfectly normal dates, but if the computer
> (Windows Control Panel --- NOT EXCEL) is set to a different format, it cannot
> be interpreted as a date by Excel and you will get the VALUE error.
>
> For example, 12/13/2004 is a perfectly valid date with US settings (mm/dd/yyyy)
> but would be invalid with UK settings (dd/mm/yyyy) since there is no 13th
> month.
>
>
> --ron
>

Hi Roger,
I shot out to the site first thing yesterday morning (Monday) and found that
the settings were in fact English(US). Having changed that to English (NZ),
it is now running beautifully. Thanks so much for your help
Sue

"Roger Govier" wrote:

> Hi Sue
>
> In addition to Ron's suggestions, I was also thinking about Text entries.
> Are the dates in I2 true dates, or text values looking like dates?
> A simple test in a blank cell of =I2+1 would resolve this, and I guess could
> be carried out over the phone rather than a visit - if it's not too late!!
>
> Regards
>
> Roger Govier
>
>
> Sue wrote:
> > Hi Roger,
> > My apologies. I presumed that as I had got no reply to my last post that I
> > had to start again with a new thread. Reasonably new at this. There is only
> > one computer that is not responding like it should as per my first posting.
> > The "two" is the no. of columns with the formula. I rang the people with the
> > problem PC and they confirmed that the date was for today based on your
> > suggestion.
> > Based on Ron's reply to this thread, I will need to travel out there and
> > take a visual look at what is going on.
> > Many thanks.
> > Sue
> >
> > "Roger Govier" wrote:
> >
> >
> >>Hi Sue
> >>
> >>You would be better keeping your posting in the same thread.
> >>That way, other people can see the answers that have been offered so far, an
> >>prevent duplication of effort.
> >>
> >>It sounds as though you have checked the other 2? computers (as per your
> >>original posting) but one is till returning the error.
> >>The date for today() is correct.
> >>The values in I2 is definitely before today().
> >>It is a 0 (zero) at the end of the formula, and not a capital "O"?
> >>
> >>Your formula works absolutely fine for me, and I cannot think of other
> >>possibilities, but others might. Hopefully the foregoing will bring others
> >>up to speed on where we have got to thus far.
> >>
> >>Regards
> >>
> >>Roger Govier
> >>
> >>
> >>Sue wrote:
> >>
> >>>Can someone please help me with an error message for the following formulas:
> >>>=IF(I2="",0,(DATEDIF(I2,TODAY(),"Y")>=3)+0)
> >>>=IF(I2="",0,(DATEDIF(I2,TODAY(),"Y")<=2)+0)
> >>>It has only come up on this one computer. It's fine on all others. Have
> >>>checked the computer's date based on a suggestion but the date is correct.
> >>>Can anyone please help?
> >>>Sue
> >>
>

Hi Sue
You're very welcome.
Glad you got it all sorted out.

Regards

Roger Govier


Sue wrote:
> Hi Roger,
> I shot out to the site first thing yesterday morning (Monday) and found that
> the settings were in fact English(US). Having changed that to English (NZ),
> it is now running beautifully. Thanks so much for your help
> Sue
>
> "Roger Govier" wrote:
>
>
>>Hi Sue
>>
>>In addition to Ron's suggestions, I was also thinking about Text entries.
>>Are the dates in I2 true dates, or text values looking like dates?
>>A simple test in a blank cell of =I2+1 would resolve this, and I guess could
>>be carried out over the phone rather than a visit - if it's not too late!!
>>
>>Regards
>>
>>Roger Govier
>>
>>
>>Sue wrote:
>>
>>>Hi Roger,
>>>My apologies. I presumed that as I had got no reply to my last post that I
>>>had to start again with a new thread. Reasonably new at this. There is only
>>>one computer that is not responding like it should as per my first posting.
>>>The "two" is the no. of columns with the formula. I rang the people with the
>>>problem PC and they confirmed that the date was for today based on your
>>>suggestion.
>>>Based on Ron's reply to this thread, I will need to travel out there and
>>>take a visual look at what is going on.
>>>Many thanks.
>>>Sue
>>>
>>>"Roger Govier" wrote:
>>>
>>>
>>>
>>>>Hi Sue
>>>>
>>>>You would be better keeping your posting in the same thread.
>>>>That way, other people can see the answers that have been offered so far, an
>>>>prevent duplication of effort.
>>>>
>>>>It sounds as though you have checked the other 2? computers (as per your
>>>>original posting) but one is till returning the error.
>>>>The date for today() is correct.
>>>>The values in I2 is definitely before today().
>>>>It is a 0 (zero) at the end of the formula, and not a capital "O"?
>>>>
>>>>Your formula works absolutely fine for me, and I cannot think of other
>>>>possibilities, but others might. Hopefully the foregoing will bring others
>>>>up to speed on where we have got to thus far.
>>>>
>>>>Regards
>>>>
>>>>Roger Govier
>>>>
>>>>
>>>>Sue wrote:
>>>>
>>>>
>>>>>Can someone please help me with an error message for the following formulas:
>>>>>=IF(I2="",0,(DATEDIF(I2,TODAY(),"Y")>=3)+0)
>>>>>=IF(I2="",0,(DATEDIF(I2,TODAY(),"Y")<=2)+0)
>>>>>It has only come up on this one computer. It's fine on all others. Have
>>>>>checked the computer's date based on a suggestion but the date is correct.
>>>>>Can anyone please help?
>>>>>Sue
>>>>