| Home |
| Search |
| Today's Posts |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date String To Serial | Excel Discussion (Misc queries) | |||
| date serial | Excel Programming | |||
| How do I convert Date serial number to date | Excel Worksheet Functions | |||
| A VBA function to give serial date for date and time? | Excel Programming | |||
| time serial number problem | Excel Programming | |||
Threaded Mode
