Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Format Correct, But Formula Error
Hi All, this is driving me crazy...if anyone can help i'd be grateful. My formula is: =ROUND((I4 - MySheetY3) / 365,0) so i'm just trying to subtract one date from the other and then dividing by 365to get the rounded number of years difference However, this results in an #value error. The trace error shows: =Round (( 35828 - "31/06/1948" / 365, 0) When looking at the trace error bit, the first date (I4) is being taken as the serial number, not the date, whereas the date in Y3 is being seen as a date. So thinking it was a formating thing, I checked the cell formating and it is in a date format. I've tried deleting all the columns and starting again, painting formats, changing formats...everything! Any ideas? cheers, John -- john_mc ------------------------------------------------------------------------ john_mc's Profile: http://www.excelforum.com/member.php...o&userid=32013 View this thread: http://www.excelforum.com/showthread...hreadid=531404 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Format Correct, But Formula Error
Hi!
The problem is: "31/06/1948" There is no June 31st so the "date" is being evaluated as a TEXT string. Biff "john_mc" wrote in message ... Hi All, this is driving me crazy...if anyone can help i'd be grateful. My formula is: =ROUND((I4 - MySheetY3) / 365,0) so i'm just trying to subtract one date from the other and then dividing by 365to get the rounded number of years difference However, this results in an #value error. The trace error shows: =Round (( 35828 - "31/06/1948" / 365, 0) When looking at the trace error bit, the first date (I4) is being taken as the serial number, not the date, whereas the date in Y3 is being seen as a date. So thinking it was a formating thing, I checked the cell formating and it is in a date format. I've tried deleting all the columns and starting again, painting formats, changing formats...everything! Any ideas? cheers, John -- john_mc ------------------------------------------------------------------------ john_mc's Profile: http://www.excelforum.com/member.php...o&userid=32013 View this thread: http://www.excelforum.com/showthread...hreadid=531404 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Date Format Problems?? | Excel Discussion (Misc queries) | |||
Ignore error msgs in formula references | Excel Discussion (Misc queries) | |||
Problem with VBA returning the contents of a long formula. | Excel Discussion (Misc queries) | |||
Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions |