Date Serial Problem

[sylink]

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

 

[Ron Rosenfeld]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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

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

 

[Rick Rothstein \(MVP - VB\)]

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

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

 

[sylink]

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

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