|
Date Serial Problem
The macro below was applied to the dates given to obtain numbers of
days. Instead, it keep producing the RESULT in serial-like faction. What do I do to achieve the expected result shown below? dim tx as Date tx = Format(#1/1/2008#, "dd/mm/yyyy") Range("F" & z).FormulaR1C1 = "=RC[-1] - " & (tx) DATE 30/01/2008 31/1/2008 31/1/2007 RESULT 39477 39478 39478 EXPECTED RESULT(NO OF DAYS) 29 30 30 30 |
Date Serial Problem
On Thu, 14 Feb 2008 05:45:13 -0800 (PST), sylink wrote:
The macro below was applied to the dates given to obtain numbers of days. Instead, it keep producing the RESULT in serial-like faction. What do I do to achieve the expected result shown below? dim tx as Date tx = Format(#1/1/2008#, "dd/mm/yyyy") Range("F" & z).FormulaR1C1 = "=RC[-1] - " & (tx) DATE 30/01/2008 31/1/2008 31/1/2007 RESULT 39477 39478 39478 EXPECTED RESULT(NO OF DAYS) 29 30 30 30 You post so little it's hard to know where the problem is. If your third result is a typo, then you are not subtracting tx from date, as the serial numbers you return are merely the original date as stored by Excel. Perhaps that will give you an idea where to look for the problem. e.g. 39477 = January 30, 2008 39478 = January 31, 2008 31/1/2007 though, should be 39113 --ron |
Date Serial Problem
I'm not completely sure how you want your program to actually work, but if
you replace this... Range("F" & z).FormulaR1C1 = "=RC[-1] - " & (tx) with this.... Range("F" & z).FormulaR1C1 = "=RC[-1] - DATEVALUE(""" & tx & """)" I think you will get the results you are looking for. Rick "sylink" wrote in message ... The macro below was applied to the dates given to obtain numbers of days. Instead, it keep producing the RESULT in serial-like faction. What do I do to achieve the expected result shown below? dim tx as Date tx = Format(#1/1/2008#, "dd/mm/yyyy") Range("F" & z).FormulaR1C1 = "=RC[-1] - " & (tx) DATE 30/01/2008 31/1/2008 31/1/2007 RESULT 39477 39478 39478 EXPECTED RESULT(NO OF DAYS) 29 30 30 30 |
Date Serial Problem
On Feb 14, 7:08 pm, "Rick Rothstein \(MVP - VB\)"
wrote: I'm not completely sure how you want your program to actually work, but if you replace this... Range("F" & z).FormulaR1C1 = "=RC[-1] - " & (tx) with this.... Range("F" & z).FormulaR1C1 = "=RC[-1] - DATEVALUE(""" & tx & """)" I think you will get the results you are looking for. Rick "sylink" wrote in message ... The macro below was applied to the dates given to obtain numbers of days. Instead, it keep producing the RESULT in serial-like faction. What do I do to achieve the expected result shown below? dim tx as Date tx = Format(#1/1/2008#, "dd/mm/yyyy") Range("F" & z).FormulaR1C1 = "=RC[-1] - " & (tx) DATE 30/01/2008 31/1/2008 31/1/2007 RESULT 39477 39478 39478 EXPECTED RESULT(NO OF DAYS) 29 30 30 30 Thank you Rick for that tip. It worked-i got 3days for the eg below. But when I added this (rc[-3]) =100 as shown: Range("F" & z).FormulaR1C1 = "=RC[-1] - DATEVALUE(""" & tx & """)*RC[-3]" i got -3905349 for Range("F" & z).FormulaR1C1 instead of 30 What else should I do? Please note below: rc[3] contains 100 tx = Format(#1/1/2008#, "dd/mm/yyyy") RC[-1] contain 04/01/2008 Cheers |
Date Serial Problem
I'm not completely sure how you want your program to actually work, but
if you replace this... Range("F" & z).FormulaR1C1 = "=RC[-1] - " & (tx) with this.... Range("F" & z).FormulaR1C1 = "=RC[-1] - DATEVALUE(""" & tx & """)" I think you will get the results you are looking for. Rick "sylink" wrote in message ... The macro below was applied to the dates given to obtain numbers of days. Instead, it keep producing the RESULT in serial-like faction. What do I do to achieve the expected result shown below? dim tx as Date tx = Format(#1/1/2008#, "dd/mm/yyyy") Range("F" & z).FormulaR1C1 = "=RC[-1] - " & (tx) DATE 30/01/2008 31/1/2008 31/1/2007 RESULT 39477 39478 39478 EXPECTED RESULT(NO OF DAYS) 29 30 30 30 Thank you Rick for that tip. It worked-i got 3days for the eg below. But when I added this (rc[-3]) =100 as shown: Range("F" & z).FormulaR1C1 = "=RC[-1] - DATEVALUE(""" & tx & """)*RC[-3]" i got -3905349 for Range("F" & z).FormulaR1C1 instead of 30 What else should I do? Please note below: rc[3] contains 100 tx = Format(#1/1/2008#, "dd/mm/yyyy") RC[-1] contain 04/01/2008 Again, I have no idea how your formula is supposed to work; however, if RC[-3] (or maybe RC[3]... you used both in your above message) contains 100 and you multiply the date from tx by it, you no longer have a date (it is 100-fold too big), so subtracting it from a date in RC[-1] is a meaningless thing to do. So, in some manner, your formula is incorrect. My guess is you want to multiply the 100 times the difference between the two dates. If that is the case, your formula statement would be this... Range("F10").FormulaR1C1 = "=(RC[-1] - DATEVALUE(""" & tx & """))*RC[-3]" But, again, that is just a guess at what you are trying to do. Rick |
Date Serial Problem
On Feb 15, 6:28 pm, "Rick Rothstein \(MVP - VB\)"
wrote: I'm not completely sure how you want your program to actually work, but if you replace this... Range("F" & z).FormulaR1C1 = "=RC[-1] - " & (tx) with this.... Range("F" & z).FormulaR1C1 = "=RC[-1] - DATEVALUE(""" & tx & """)" I think you will get the results you are looking for. Rick "sylink" wrote in message ... The macro below was applied to the dates given to obtain numbers of days. Instead, it keep producing the RESULT in serial-like faction. What do I do to achieve the expected result shown below? dim tx as Date tx = Format(#1/1/2008#, "dd/mm/yyyy") Range("F" & z).FormulaR1C1 = "=RC[-1] - " & (tx) DATE 30/01/2008 31/1/2008 31/1/2007 RESULT 39477 39478 39478 EXPECTED RESULT(NO OF DAYS) 29 30 30 30 Thank you Rick for that tip. It worked-i got 3days for the eg below. But when I added this (rc[-3]) =100 as shown: Range("F" & z).FormulaR1C1 = "=RC[-1] - DATEVALUE(""" & tx & """)*RC[-3]" i got -3905349 for Range("F" & z).FormulaR1C1 instead of 30 What else should I do? Please note below: rc[3] contains 100 tx = Format(#1/1/2008#, "dd/mm/yyyy") RC[-1] contain 04/01/2008 Again, I have no idea how your formula is supposed to work; however, if RC[-3] (or maybe RC[3]... you used both in your above message) contains 100 and you multiply the date from tx by it, you no longer have a date (it is 100-fold too big), so subtracting it from a date in RC[-1] is a meaningless thing to do. So, in some manner, your formula is incorrect. My guess is you want to multiply the 100 times the difference between the two dates. If that is the case, your formula statement would be this... Range("F10").FormulaR1C1 = "=(RC[-1] - DATEVALUE(""" & tx & """))*RC[-3]" But, again, that is just a guess at what you are trying to do. Rick Thank you Rick |
| All times are GMT +1. The time now is 12:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com