If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
##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 |
#2
|
|||
|
|||
##value error
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 |
#3
|
|||
|
|||
##value error
On Thu, 10 Nov 2005 11:56:10 -0800, 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 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 |
#4
|
|||
|
|||
##value error
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 |
#5
|
|||
|
|||
##value error
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 |
#6
|
|||
|
|||
##value error
On Fri, 11 Nov 2005 12:39:52 +0000, 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 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 |
#7
|
|||
|
|||
##value error
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 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 |
#8
|
|||
|
|||
##value error
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 |
#9
|
|||
|
|||
##value error
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 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 |
#10
|
|||
|
|||
##value error
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 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 |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Reserved Error - 1104 - Works in 97 not in XP? | beveritt | General Discussion | 0 | August 25th, 2005 05:07 PM |
Error message different in MDB and MDE | david epsom dot com dot au | General Discussion | 1 | September 21st, 2004 12:47 AM |
Continual Error 1321 Trying to Install Office 2003 | Chad Harris | General Discussions | 9 | June 11th, 2004 08:19 AM |
Product Key for Office XP | P.G.Indiana | Setup, Installing & Configuration | 1 | June 7th, 2004 03:22 AM |
Error #1321 MOS 2003 Setup | Chad Harris | Setup, Installing & Configuration | 1 | June 7th, 2004 12:22 AM |