datevalue returns #value
in [Functions]
|
From: Michele R on 28 Mar 2010 10:08 Hi - I;ve had a look through and cant find a solution to this problem. I have a column that returns a Quarter (of financial year) dependent on the date of an invoice. So an invoice date of 13/04/2009 returns Q1 in the column. I am doing this by converting the date by =datevalue(), and a nested IF formula based on those values. It works great for the dates that I have already, but if I add a row, or change a date (for instance July's rent was invoiced 30/06/2009, but I need to change that date to 01/07/2009, or even 30/07/2009 to make July's rent fall into Q2) it returns #Value! instead of the number. I have checked everything I can think of to do with the format, but I cant come up with a reason. I am using 2007 on XP. Thanks for any help. -- Thanks Michele
From: tompl on 28 Mar 2010 10:34 My guess is that the cell into which you are entering the date is formatted text and the formula does not recognize text. Perhaps, if you changed the format to general or date it would solve the problem. It does puzzle that you are not getting errors with 13/04/2009 as that is not a valid date. Tom
From: tompl on 28 Mar 2010 10:37 Your date format is Day, Month, Year; not Month, Day, Year. Input your date as 07/01/2009 and it should work. rin
From: tompl on 28 Mar 2010 10:41 Although, that date should work anyway. I give up! Tom
From: Fred Smith on 28 Mar 2010 11:04
Why use Datevalue at all? Datevalue is to convert text to dates. When you use Datevalue on a date, you get a #Value error. Forget Datevalue. Store all your dates as date, and just use the cell value. Regards, Fred "Michele R" <MicheleR(a)discussions.microsoft.com> wrote in message news:9844C437-A27D-4077-B4E4-B0ABC243B7C6(a)microsoft.com... > Hi - I;ve had a look through and cant find a solution to this problem. > > I have a column that returns a Quarter (of financial year) dependent on > the > date of an invoice. So an invoice date of 13/04/2009 returns Q1 in the > column. I am doing this by converting the date by =datevalue(), and a > nested > IF formula based on those values. It works great for the dates that I have > already, but if I add a row, or change a date (for instance July's rent > was > invoiced 30/06/2009, but I need to change that date to 01/07/2009, or even > 30/07/2009 to make July's rent fall into Q2) it returns #Value! instead of > the number. I have checked everything I can think of to do with the > format, > but I cant come up with a reason. I am using 2007 on XP. > > Thanks for any help. > > -- > Thanks > Michele |