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

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

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

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

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

"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

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

"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

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

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

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

"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

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

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

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

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.

--
Regards,
Tom Ogilvy


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

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

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

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

--
Regards,
Tom Ogilvy


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