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