datevalue returns #value

[Michele R]

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.

 

[tompl]

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

 

[tompl]

Your date format is Day, Month, Year; not Month, Day, Year. Input your date
as 07/01/2009 and it should work.

rin

 

[tompl]

Although, that date should work anyway.
I give up!

Tom

 

[Fred Smith]

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

 

[Herbert Seidenberg]

Excel 2007 PivotTable
No formulas needed.
http://www.mediafire.com/file/4yztjnzjwqy/03_26_10.xlsx
Pdf preview:
http://www.mediafire.com/file/yhxwml1jnje/03_26_10.pdf