+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Ed
    Guest

    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



  2. #2
    Tom Ogilvy
    Guest

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

    post the formula you want in the cell.

    --
    Regards,
    Tom Ogilvy

    "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
    news:[email protected]...
    > 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
    >
    >




  3. #3
    PaulD
    Guest

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

    "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
    news:[email protected]...
    : 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



  4. #4
    PaulD
    Guest

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


    "PaulD" <nospam> wrote in message
    news:[email protected]...
    : "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
    : news:[email protected]...
    : : 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



  5. #5
    Ed
    Guest

    Re: 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
    news:[email protected]...
    >
    > "PaulD" <nospam> wrote in message
    > news:[email protected]...
    > : "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
    > : news:[email protected]...
    > : : 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
    >
    >




  6. #6
    PaulD
    Guest

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

    "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
    news:[email protected]...
    : 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



  7. #7
    Ed
    Guest

    Re: 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
    news:[email protected]...
    > "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
    > news:[email protected]...
    > : 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
    >
    >




  8. #8
    Tom Ogilvy
    Guest

    Re: 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" <ed_millis@NO_SPAM.yahoo.com> wrote in message
    news:[email protected]...
    > 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
    > news:[email protected]...
    > > "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
    > > news:[email protected]...
    > > : 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
    > >
    > >

    >
    >




  9. #9
    Ed
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <ed_millis@NO_SPAM.yahoo.com> wrote in message
    > news:[email protected]...
    > > 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
    > > news:[email protected]...
    > > > "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
    > > > news:[email protected]...
    > > > : 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
    > > >
    > > >

    > >
    > >

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1