|
Count Between dates
How can I count between two dates when format for the dates is DD-MM-YY
I need to know how many days are between 21/04/09 and 28/04/09 The solution should be 8 if I use =(A2-A1+1) but Im getting #VALUE! Im used format = Custom = dd/mm/yy, also General and Number but they are not working either. Any suggestions? -- Juan Carlos |
Count Between dates
That is because your values are text strings rather than dates. Convert them
to dates first. -- Gary''s Student - gsnu200904 "juancarlos" wrote: How can I count between two dates when format for the dates is DD-MM-YY I need to know how many days are between 21/04/09 and 28/04/09 The solution should be 8 if I use =(A2-A1+1) but Im getting #VALUE! Im used format = Custom = dd/mm/yy, also General and Number but they are not working either. Any suggestions? -- Juan Carlos |
Count Between dates
Gary,
I converted them to dates but I got the same result. Juan "Gary''s Student" wrote: That is because your values are text strings rather than dates. Convert them to dates first. -- Gary''s Student - gsnu200904 "juancarlos" wrote: How can I count between two dates when format for the dates is DD-MM-YY I need to know how many days are between 21/04/09 and 28/04/09 The solution should be 8 if I use =(A2-A1+1) but Im getting #VALUE! Im used format = Custom = dd/mm/yy, also General and Number but they are not working either. Any suggestions? -- Juan Carlos |
Count Between dates
If you are getting a #VALUE! error I'd be willing to bet that you haven't
changed them from text to dates. If they are dates, =ISNUMBER(A1) and =ISNUMBER(A2) will return TRUE, and =ISTEXT(A1) and =ISTEXT(A2) will return FALSE. Perhaps you'd care to tell us what those formulae do return? -- David Biddulph "Juan Carlos" wrote in message ... Gary, I converted them to dates but I got the same result. Juan "Gary''s Student" wrote: That is because your values are text strings rather than dates. Convert them to dates first. -- Gary''s Student - gsnu200904 "juancarlos" wrote: How can I count between two dates when format for the dates is DD-MM-YY I need to know how many days are between 21/04/09 and 28/04/09 The solution should be 8 if I use =(A2-A1+1) but I'm getting #VALUE! I'm used format = Custom = dd/mm/yy, also General and Number but they are not working either. Any suggestions? -- Juan Carlos |
Count Between dates
David,
Im getting false for =ISNUMBER(A1)(A2) and True for =ISTEXT(A1)(A2). I think my problem have to do with how the dates are entered. I dont think that excel recognized the format dd/mm/yy to calculate number of days between dates. Im going to convert the A1 and A2 to dates by adding 2 more columns using the following formula =DATE(RIGHT(D60,2)+2000,MID(D60,4,2),LEFT(D60,2)) and then will do the calculation from there. Unless you have another solution for me Juan Juan "David Biddulph" wrote: If you are getting a #VALUE! error I'd be willing to bet that you haven't changed them from text to dates. If they are dates, =ISNUMBER(A1) and =ISNUMBER(A2) will return TRUE, and =ISTEXT(A1) and =ISTEXT(A2) will return FALSE. Perhaps you'd care to tell us what those formulae do return? -- David Biddulph "Juan Carlos" wrote in message ... Gary, I converted them to dates but I got the same result. Juan "Gary''s Student" wrote: That is because your values are text strings rather than dates. Convert them to dates first. -- Gary''s Student - gsnu200904 "juancarlos" wrote: How can I count between two dates when format for the dates is DD-MM-YY I need to know how many days are between 21/04/09 and 28/04/09 The solution should be 8 if I use =(A2-A1+1) but I'm getting #VALUE! I'm used format = Custom = dd/mm/yy, also General and Number but they are not working either. Any suggestions? -- Juan Carlos |
Count Between dates
Excel has no trouble recognizing dd/mm/yy as a date. It doesn't require 4
digits for the year. If you entered the date, then the likely problem is that your cell was formatted as text beforehand. Try this: 1. Format the cell as a date 2. Re-enter the date. Now try your formula. Regards, Fred "Juan Carlos" wrote in message ... David, Im getting false for =ISNUMBER(A1)(A2) and True for =ISTEXT(A1)(A2). I think my problem have to do with how the dates are entered. I dont think that excel recognized the format dd/mm/yy to calculate number of days between dates. Im going to convert the A1 and A2 to dates by adding 2 more columns using the following formula =DATE(RIGHT(D60,2)+2000,MID(D60,4,2),LEFT(D60,2)) and then will do the calculation from there. Unless you have another solution for me Juan Juan "David Biddulph" wrote: If you are getting a #VALUE! error I'd be willing to bet that you haven't changed them from text to dates. If they are dates, =ISNUMBER(A1) and =ISNUMBER(A2) will return TRUE, and =ISTEXT(A1) and =ISTEXT(A2) will return FALSE. Perhaps you'd care to tell us what those formulae do return? -- David Biddulph "Juan Carlos" wrote in message ... Gary, I converted them to dates but I got the same result. Juan "Gary''s Student" wrote: That is because your values are text strings rather than dates. Convert them to dates first. -- Gary''s Student - gsnu200904 "juancarlos" wrote: How can I count between two dates when format for the dates is DD-MM-YY I need to know how many days are between 21/04/09 and 28/04/09 The solution should be 8 if I use =(A2-A1+1) but I'm getting #VALUE! I'm used format = Custom = dd/mm/yy, also General and Number but they are not working either. Any suggestions? -- Juan Carlos |
Count Between dates
Fred wrote on Thu, 24 Sep 2009 13:58:01 -0600:
If you entered the date, then the likely problem is that your cell was formatted as text beforehand. Try this: 1. Format the cell as a date 2. Re-enter the date. Now try your formula. Regards, Fred "Juan Carlos" wrote in message ... David, Im getting false for =ISNUMBER(A1)(A2) and True for =ISTEXT(A1)(A2). I think my problem have to do with how the dates are entered. I dont think that excel recognized the format dd/mm/yy to calculate number of days between dates. Im going to convert the A1 and A2 to dates by adding 2 more columns using the following formula =DATE(RIGHT(D60,2)+2000,MID(D60,4,2),LEFT(D60,2)) and then will do the calculation from there. Unless you have another solution for me Juan Juan "David Biddulph" wrote: If you are getting a #VALUE! error I'd be willing to bet that you haven't changed them from text to dates. If they are dates, =ISNUMBER(A1) and =ISNUMBER(A2) will return TRUE, and =ISTEXT(A1) and =ISTEXT(A2) will return FALSE. Perhaps you'd care to tell us what those formulae do return? -- David Biddulph "Juan Carlos" wrote Is the problem perhaps using British date format and US Excel. I just tried it with two dates, one pair, British dd/mm/yy, gave #VALUE in DATEDIF. Another pair, US format, mm/dd/yy, worked (EXCEL 2002). -- James Silverton Potomac, Maryland Email, with obvious alterations: not.jim.silverton.at.verizon.not |
Count Between dates
The Isnumber and Istext results have nothing to do with US or UK date
formats. The problem is the cell is formatted as text. What formula did you use to get a #Value error in Datedif? Regards, Fred. "James Silverton" wrote in message ... Fred wrote on Thu, 24 Sep 2009 13:58:01 -0600: If you entered the date, then the likely problem is that your cell was formatted as text beforehand. Try this: 1. Format the cell as a date 2. Re-enter the date. Now try your formula. Regards, Fred "Juan Carlos" wrote in message ... David, Im getting false for =ISNUMBER(A1)(A2) and True for =ISTEXT(A1)(A2). I think my problem have to do with how the dates are entered. I dont think that excel recognized the format dd/mm/yy to calculate number of days between dates. Im going to convert the A1 and A2 to dates by adding 2 more columns using the following formula =DATE(RIGHT(D60,2)+2000,MID(D60,4,2),LEFT(D60,2)) and then will do the calculation from there. Unless you have another solution for me Juan Juan "David Biddulph" wrote: If you are getting a #VALUE! error I'd be willing to bet that you haven't changed them from text to dates. If they are dates, =ISNUMBER(A1) and =ISNUMBER(A2) will return TRUE, and =ISTEXT(A1) and =ISTEXT(A2) will return FALSE. Perhaps you'd care to tell us what those formulae do return? -- David Biddulph "Juan Carlos" wrote Is the problem perhaps using British date format and US Excel. I just tried it with two dates, one pair, British dd/mm/yy, gave #VALUE in DATEDIF. Another pair, US format, mm/dd/yy, worked (EXCEL 2002). -- James Silverton Potomac, Maryland Email, with obvious alterations: not.jim.silverton.at.verizon.not |
Count Between dates
The problem is not that the cell is *formatted* as text; the problem is
that the cell *contents* are text. Cell formatting governs how a number is displayed, but the reason for the #VALUE! error is that the contents are not a number but text. The formula that gave the original #VALUE! error was given at the start of the thread and was =(A2-A1+1) There are a number of possible reasons for the content being text, and use of the wrong Windows Regional Options could be one of them. If the options are set to expect UK date and one types in 09/25/09, the cell contents will be treated as text (and would thus give a #VALUE! error in the DATEDIF situation which James reported, or in the original subtraction formula). If the number typed in is 09/03/09, then it wouldn't result in text but would merely be interpreted as 9th March instead of 3rd September. Another possibility for the reason for the cell contents being text is the cell being formatted as text before the number is typed in, as you mentioned in your previous post. Another possibility is that the data (perhaps imported from another application) includes spaces or non-breaking spaces. -- David Biddulph "Fred Smith" wrote in message ... The Isnumber and Istext results have nothing to do with US or UK date formats. The problem is the cell is formatted as text. What formula did you use to get a #Value error in Datedif? Regards, Fred. "James Silverton" wrote in message ... Fred wrote on Thu, 24 Sep 2009 13:58:01 -0600: If you entered the date, then the likely problem is that your cell was formatted as text beforehand. Try this: 1. Format the cell as a date 2. Re-enter the date. Now try your formula. Regards, Fred "Juan Carlos" wrote in message ... David, I'm getting false for =ISNUMBER(A1)(A2) and True for =ISTEXT(A1)(A2). I think my problem have to do with how the dates are entered. I don't think that excel recognized the format dd/mm/yy to calculate number of days between dates. I'm going to convert the A1 and A2 to dates by adding 2 more columns using the following formula =DATE(RIGHT(D60,2)+2000,MID(D60,4,2),LEFT(D60,2)) and then will do the calculation from there. Unless you have another solution for me Juan Juan "David Biddulph" wrote: If you are getting a #VALUE! error I'd be willing to bet that you haven't changed them from text to dates. If they are dates, =ISNUMBER(A1) and =ISNUMBER(A2) will return TRUE, and =ISTEXT(A1) and =ISTEXT(A2) will return FALSE. Perhaps you'd care to tell us what those formulae do return? -- David Biddulph "Juan Carlos" wrote Is the problem perhaps using British date format and US Excel. I just tried it with two dates, one pair, British dd/mm/yy, gave #VALUE in DATEDIF. Another pair, US format, mm/dd/yy, worked (EXCEL 2002). -- James Silverton Potomac, Maryland Email, with obvious alterations: not.jim.silverton.at.verizon.not |
Count Between dates
David,
I think you are right about the Windows Regional Options setting, I am using U.S settings. Thanks again for the feedback Juan "David Biddulph" wrote: The problem is not that the cell is *formatted* as text; the problem is that the cell *contents* are text. Cell formatting governs how a number is displayed, but the reason for the #VALUE! error is that the contents are not a number but text. The formula that gave the original #VALUE! error was given at the start of the thread and was =(A2-A1+1) There are a number of possible reasons for the content being text, and use of the wrong Windows Regional Options could be one of them. If the options are set to expect UK date and one types in 09/25/09, the cell contents will be treated as text (and would thus give a #VALUE! error in the DATEDIF situation which James reported, or in the original subtraction formula). If the number typed in is 09/03/09, then it wouldn't result in text but would merely be interpreted as 9th March instead of 3rd September. Another possibility for the reason for the cell contents being text is the cell being formatted as text before the number is typed in, as you mentioned in your previous post. Another possibility is that the data (perhaps imported from another application) includes spaces or non-breaking spaces. -- David Biddulph "Fred Smith" wrote in message ... The Isnumber and Istext results have nothing to do with US or UK date formats. The problem is the cell is formatted as text. What formula did you use to get a #Value error in Datedif? Regards, Fred. "James Silverton" wrote in message ... Fred wrote on Thu, 24 Sep 2009 13:58:01 -0600: If you entered the date, then the likely problem is that your cell was formatted as text beforehand. Try this: 1. Format the cell as a date 2. Re-enter the date. Now try your formula. Regards, Fred "Juan Carlos" wrote in message ... David, I'm getting false for =ISNUMBER(A1)(A2) and True for =ISTEXT(A1)(A2). I think my problem have to do with how the dates are entered. I don't think that excel recognized the format dd/mm/yy to calculate number of days between dates. I'm going to convert the A1 and A2 to dates by adding 2 more columns using the following formula =DATE(RIGHT(D60,2)+2000,MID(D60,4,2),LEFT(D60,2)) and then will do the calculation from there. Unless you have another solution for me Juan Juan "David Biddulph" wrote: If you are getting a #VALUE! error I'd be willing to bet that you haven't changed them from text to dates. If they are dates, =ISNUMBER(A1) and =ISNUMBER(A2) will return TRUE, and =ISTEXT(A1) and =ISTEXT(A2) will return FALSE. Perhaps you'd care to tell us what those formulae do return? -- David Biddulph "Juan Carlos" wrote Is the problem perhaps using British date format and US Excel. I just tried it with two dates, one pair, British dd/mm/yy, gave #VALUE in DATEDIF. Another pair, US format, mm/dd/yy, worked (EXCEL 2002). -- James Silverton Potomac, Maryland Email, with obvious alterations: not.jim.silverton.at.verizon.not |
Count Between dates
Fred wrote on Fri, 25 Sep 2009 09:13:42 -0600:
.. "James Silverton" wrote in message ... Fred wrote on Thu, 24 Sep 2009 13:58:01 -0600: The Isnumber and Istext results have nothing to do with US or UK date formats. The problem is the cell is formatted as text. What formula did you use to get a #Value error in Datedif? I had in column A: 5/9/2009, 9/25/2009, 9/5/2009, 25/9/2009 =DATEDIF(A1,A2,"d") gave the correct answer. =DATEDIF(A3, A4,"d") gave the error. -- James Silverton Potomac, Maryland Email, with obvious alterations: not.jim.silverton.at.verizon.not |
Count Between dates
Your value error is because A4 is text. Anything that Excel does not
recognize as a valid date is considered text. Excel takes its input date format from Windows' Regional Settings. Formatting a cell in Excel affects only the output (how it's displayed). No matter how the date is formatted in Excel, inputting a date is controlled by the Windows setting. Regards, Fred. "James Silverton" wrote in message ... Fred wrote on Fri, 25 Sep 2009 09:13:42 -0600: . "James Silverton" wrote in message ... Fred wrote on Thu, 24 Sep 2009 13:58:01 -0600: The Isnumber and Istext results have nothing to do with US or UK date formats. The problem is the cell is formatted as text. What formula did you use to get a #Value error in Datedif? I had in column A: 5/9/2009, 9/25/2009, 9/5/2009, 25/9/2009 =DATEDIF(A1,A2,"d") gave the correct answer. =DATEDIF(A3, A4,"d") gave the error. -- James Silverton Potomac, Maryland Email, with obvious alterations: not.jim.silverton.at.verizon.not |
Count Between dates
I posted a question 3 times on the general question, but I dont see it on
the screen. Any idea whats wrong? "David Biddulph" wrote: The problem is not that the cell is *formatted* as text; the problem is that the cell *contents* are text. Cell formatting governs how a number is displayed, but the reason for the #VALUE! error is that the contents are not a number but text. The formula that gave the original #VALUE! error was given at the start of the thread and was =(A2-A1+1) There are a number of possible reasons for the content being text, and use of the wrong Windows Regional Options could be one of them. If the options are set to expect UK date and one types in 09/25/09, the cell contents will be treated as text (and would thus give a #VALUE! error in the DATEDIF situation which James reported, or in the original subtraction formula). If the number typed in is 09/03/09, then it wouldn't result in text but would merely be interpreted as 9th March instead of 3rd September. Another possibility for the reason for the cell contents being text is the cell being formatted as text before the number is typed in, as you mentioned in your previous post. Another possibility is that the data (perhaps imported from another application) includes spaces or non-breaking spaces. -- David Biddulph "Fred Smith" wrote in message ... The Isnumber and Istext results have nothing to do with US or UK date formats. The problem is the cell is formatted as text. What formula did you use to get a #Value error in Datedif? Regards, Fred. "James Silverton" wrote in message ... Fred wrote on Thu, 24 Sep 2009 13:58:01 -0600: If you entered the date, then the likely problem is that your cell was formatted as text beforehand. Try this: 1. Format the cell as a date 2. Re-enter the date. Now try your formula. Regards, Fred "Juan Carlos" wrote in message ... David, I'm getting false for =ISNUMBER(A1)(A2) and True for =ISTEXT(A1)(A2). I think my problem have to do with how the dates are entered. I don't think that excel recognized the format dd/mm/yy to calculate number of days between dates. I'm going to convert the A1 and A2 to dates by adding 2 more columns using the following formula =DATE(RIGHT(D60,2)+2000,MID(D60,4,2),LEFT(D60,2)) and then will do the calculation from there. Unless you have another solution for me Juan Juan "David Biddulph" wrote: If you are getting a #VALUE! error I'd be willing to bet that you haven't changed them from text to dates. If they are dates, =ISNUMBER(A1) and =ISNUMBER(A2) will return TRUE, and =ISTEXT(A1) and =ISTEXT(A2) will return FALSE. Perhaps you'd care to tell us what those formulae do return? -- David Biddulph "Juan Carlos" wrote Is the problem perhaps using British date format and US Excel. I just tried it with two dates, one pair, British dd/mm/yy, gave #VALUE in DATEDIF. Another pair, US format, mm/dd/yy, worked (EXCEL 2002). -- James Silverton Potomac, Maryland Email, with obvious alterations: not.jim.silverton.at.verizon.not |
Count Between dates
Fred wrote on Fri, 25 Sep 2009 14:56:21 -0600:
Excel takes its input date format from Windows' Regional Settings. Formatting a cell in Excel affects only the output (how it's displayed). No matter how the date is formatted in Excel, inputting a date is controlled by the Windows setting. Regards, Fred. "James Silverton" wrote in message ... Fred wrote on Fri, 25 Sep 2009 09:13:42 -0600: . "James Silverton" wrote in message ... Fred wrote on Thu, 24 Sep 2009 13:58:01 -0600: The Isnumber and Istext results have nothing to do with US or UK date formats. The problem is the cell is formatted as text. What formula did you use to get a #Value error in Datedif? I had in column A: 5/9/2009, 9/25/2009, 9/5/2009, 25/9/2009 =DATEDIF(A1,A2,"d") gave the correct answer. =DATEDIF(A3, A4,"d") gave the error. -- Your value error is because A4 is text. Anything that Excel does not recognize as a valid date is considered text. Excel takes its input date format from Windows' Regional Settings. Formatting a cell in Excel affects only the output (how it's displayed). No matter how the date is formatted in Excel, inputting a date is controlled by he Windows setting. I don't get this! As i said originally, A4 is only not a date if you are using the US convention with month/day/year. -- James Silverton Potomac, Maryland Email, with obvious alterations: not.jim.silverton.at.verizon.not |
Count Between dates
You appear to be using Microsoft's web interface to the newsgroup, and it's
known to be unreliable. You'll be better off accessing the newsgroup directly through a news server, or alternatively there is a good searchable archive through Google, which will also allow you to post. Newsgroup hints: a.. http://www.cpearson.com/excel/HintsA...roupUsers.aspx b.. http://groups.google.com/group/micro...lic.excel.misc -- David Biddulph Juan Carlos wrote: I posted a question 3 times on the general question, but I don't see it on the screen. Any idea what's wrong? .... |
| All times are GMT +1. The time now is 12:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com