WS formula for Julian date not converting well to VBA??

[Ed]

I found the formula on Chip Pearson's site for converting a date to a Julian
date. When I try to use it as a VBA-inserted formula, it's not working out.
I keep getting an error message telling me "Expected: a close parenthesis
after the second YEAR". But doing that doesn't work. Can someone point me
to my error?

ws.Range("E1").Formula = "AR"& RIGHT(YEAR(Range("B1")),2)&
TEXT(Range("B1")-DATE(YEAR(Range("B1")),1,0),"000")

TIA
Ed

 

[Tom Ogilvy]

post the formula you want in the cell.

 

[PaulD]

: I found the formula on Chip Pearson's site for converting a date to a
Julian
: date. When I try to use it as a VBA-inserted formula, it's not working
out.
: I keep getting an error message telling me "Expected: a close parenthesis
: after the second YEAR". But doing that doesn't work. Can someone point
me
: to my error?
:
: ws.Range("E1").Formula = "AR"& RIGHT(YEAR(Range("B1")),2)&
: TEXT(Range("B1")-DATE(YEAR(Range("B1")),1,0),"000")
:
: TIA
: Ed
:

Ed,
Chips formula is meant to be pasted directly into a cell. To mimic this
from VBA you need to slightly modify

ws.Range("E1").Formula = "= ""AR"" & RIGHT(YEAR(B1),2) &
TEXT(B1-DATE(YEAR(B1),1,0),""000"")"

Note the double quotes and don't use range as this is VBA code and not
needed for a formula in a cell

Paul D

 

[PaulD]

"PaulD" <nospam> wrote in message
: : : I found the formula on Chip Pearson's site for converting a date to a
: Julian
: : date. When I try to use it as a VBA-inserted formula, it's not working
: out.
: <snip>

: Ed,
: Chips formula is meant to be pasted directly into a cell. To mimic this
: from VBA you need to slightly modify
:
: ws.Range("E1").Formula = "= ""AR"" & RIGHT(YEAR(B1),2) &
: TEXT(B1-DATE(YEAR(B1),1,0),""000"")"

<snip>

If you don't need the formula, just the result, you could also use

Public Function JulianDate(TheDate As Date)
JulianDate = Right(Year(TheDate), 2) & Format(DatePart("y", TheDate),
"000")
End Function
Then in cell E1, enter =JulianDate(B1)

Paul D

 

[Ed]

Paul: Thanks for the help.

: Chips formula is meant to be pasted directly into a cell. To mimic this
: from VBA you need to slightly modify
:
: ws.Range("E1").Formula = "= ""AR"" & RIGHT(YEAR(B1),2) &
: TEXT(B1-DATE(YEAR(B1),1,0),""000"")"

Unfortunately, when I copy your version in, the VBE highlights TEXT and pops
up an error saying "Expected end of statement"??? (I did put it all on one
line.)

If you don't need the formula, just the result, you could also use

What I'm trying to do is use a date entered into a cell in mm/dd/yyyy format
to create a serialized number. Hence, it must be static, not dynamic - it
can't change every time I open the workbook on a new day. (That's also why
I add the "AR" to the beginning.)

Ed

 

[PaulD]

: Paul: Thanks for the help.
<snip>
: Unfortunately, when I copy your version in, the VBE highlights TEXT and
pops
: up an error saying "Expected end of statement"??? (I did put it all on
one
: line.)
<snip>

Interesting indeed. I tried to copy the code and got the same error. It
appears during copy that a " is inserted when pasting (not sure why). Make
sure you have
ws.Range("E1").Formula = "= ""AR"" & RIGHT(YEAR(B1),2) & TEXT...
and not
ws.Range("E1").Formula = "= ""AR"" & RIGHT(YEAR(B1),2) &"TEXT...

notice the quote (") after the &, make sure it is not there
Paul D

 

[Ed]

Worked perfectly, Paul! Thank you. Wonder if the extra quote had something
to do with pasting it directly into the VBE and then taking out the line
break. This time, I pasted into Word, took out the line break, then pasted
the single line into the module - no quotes.

Ed

 

[Tom Ogilvy]

when you put a string in the vbe and it does not have a closing quote, it is
added for you. Due to word wrap, this is the case you encountered I
believe.

 

[Ed]

So it'll add in a quote I don't need - but the IntelliSense doesn't
recognize ActiveSheet? Some programmer was paid to much to do the little
automatic things we all try to turn off, while ignoring a few more important
things, I think!

Ed