Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
datevalue returns #value
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
datevalue returns #value
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
datevalue returns #value
Your date format is Day, Month, Year; not Month, Day, Year. Input your date
as 07/01/2009 and it should work. rin |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
datevalue returns #value
Although, that date should work anyway. I give up! Tom |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
datevalue returns #value
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
datevalue returns #value
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index with mulitple value returns and muliple column returns | Excel Worksheet Functions | |||
Help With DATEVALUE | Excel Worksheet Functions | |||
Something other than DATEVALUE | Excel Worksheet Functions | |||
datevalue() | Excel Worksheet Functions | |||
datevalue | Excel Worksheet Functions |