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

post the formula you want in the cell.

--
Regards,
Tom Ogilvy

"Ed" 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.
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" 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.
: 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" <nospam wrote in message
...
: "Ed" 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

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" <nospam wrote in message
...

"PaulD" <nospam wrote in message
...
: "Ed" 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" wrote in message
...
: 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

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

"PaulD" <nospam wrote in message
...
"Ed" wrote in message
...
: 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