Dismiss Notice

Welcome To CK5!

Registering is free and easy! Hope to see you on the forums soon.

Score a FREE t-shirt and membership sticker when you sign up for a Premium Membership and choose the recurring plan.

Question about formulas for the MS-Excel experts among us

Discussion in 'The Lounge' started by jarheadk5, Dec 12, 2003.

  1. jarheadk5

    jarheadk5 1/2 ton status

    Joined:
    Mar 6, 2000
    Posts:
    4,389
    Likes Received:
    0
    Location:
    PA
    I need to have a Julian date that updates itseld each day in a cell on a report I do every day in Excel. I already have a calendar date that updates itself; I've tried using the F'n paperclip to help me but that's about useless. Anyone have a formula for this?
     
  2. Goober

    Goober 1/2 ton status

    Joined:
    Apr 26, 2002
    Posts:
    2,222
    Likes Received:
    0
    Location:
    Mayberry (Auburn, WA)
    To convert a standard date to a julian date in cell A1:

    =RIGHT(YEAR(A1),2)&TEXT(A1-DATE(YEAR(A1),1,0),"000")


    This will use the last 2 characters of the YEAR of the date in cell A1 then append the number of days between the date in A1 and the 0th day of that year. The TEXT function changes the format the day 0f year number to three digits.


    Let me know how it works.

    /forums/images/graemlins/thumb.gif /forums/images/graemlins/thumb.gif /forums/images/graemlins/thumb.gif /forums/images/graemlins/thumb.gif /forums/images/graemlins/thumb.gif /forums/images/graemlins/thumb.gif
     
  3. jarheadk5

    jarheadk5 1/2 ton status

    Joined:
    Mar 6, 2000
    Posts:
    4,389
    Likes Received:
    0
    Location:
    PA
    OK, my calendar date is in cell T18, and my Julian date needs to be in P18. So would the formula be:

    =RIGHT(YEAR(T18),2)&TEXT(T18-DATE(YEAR(T18),1,0),"000")

    ???
     
  4. Goober

    Goober 1/2 ton status

    Joined:
    Apr 26, 2002
    Posts:
    2,222
    Likes Received:
    0
    Location:
    Mayberry (Auburn, WA)
    If you want the julian date in cell P18 to represent the calendar date in cell T18 then make the value of P18:

    =RIGHT(YEAR(T18),2)&TEXT(T18-DATE(YEAR(T18),1,0),"000")
     
  5. DPI

    DPI 1/2 ton status

    Joined:
    Jun 22, 2000
    Posts:
    2,792
    Likes Received:
    1
    Location:
    In the Burbs close to Tulsa, OK
    =(YEAR(T18)-2000+100)*1000+T18-DATE(YEAR(T18),"01","01")+1

    This formula will give the actual Julian Date. Goober's formalu counts the days in the date...
     
  6. DPI

    DPI 1/2 ton status

    Joined:
    Jun 22, 2000
    Posts:
    2,792
    Likes Received:
    1
    Location:
    In the Burbs close to Tulsa, OK
    This one converts Julian back to Date:

    =DATE(YEAR("01/01/"&TEXT(1900+INT(A1/1000),0)),MONTH("01/01/"&TEXT(1900+INT(A1/1000),0)),DAY("01/01/"&TEXT(1900+INT(A1/1000),0)))+MOD(A1,1000)-1
     
  7. Goober

    Goober 1/2 ton status

    Joined:
    Apr 26, 2002
    Posts:
    2,222
    Likes Received:
    0
    Location:
    Mayberry (Auburn, WA)
    [ QUOTE ]
    =(YEAR(T18)-2000+100)*1000+T18-DATE(YEAR(T18),"01","01")+1

    This formula will give the actual Julian Date. Goober's formalu counts the days in the date...

    [/ QUOTE ]


    What in the world are you talking about? Where is the counter function?

    For todays date (12/12/2003) my formula returns a value of 03346

    Your formula returns a value of 103346.00



    What is the julian date for December 12, 2003??


    /forums/images/graemlins/thinking.gif /forums/images/graemlins/thinking.gif /forums/images/graemlins/thinking.gif /forums/images/graemlins/thinking.gif /forums/images/graemlins/thinking.gif /forums/images/graemlins/thinking.gif
     
  8. jarheadk5

    jarheadk5 1/2 ton status

    Joined:
    Mar 6, 2000
    Posts:
    4,389
    Likes Received:
    0
    Location:
    PA
    Goober's formula worked. /forums/images/graemlins/thumb.gif

    OK, now I want to do a formula to subtract a past julian date (in cell Q6) from the current julian date (in P18), and have the answer displayed as a number in R6. I tried a few times myself to do it; no luck.
     
  9. Stickseler

    Stickseler 3/4 ton status

    Joined:
    Mar 23, 2001
    Posts:
    6,503
    Likes Received:
    0
    Location:
    Northern Virginia
    Wouldn't it be:

    =P18-Q6

    This would give you # of days
     
  10. 75-K5

    75-K5 3/4 ton status

    Joined:
    Apr 28, 2002
    Posts:
    5,353
    Likes Received:
    0
    Location:
    Anderson, Indiana
  11. jarheadk5

    jarheadk5 1/2 ton status

    Joined:
    Mar 6, 2000
    Posts:
    4,389
    Likes Received:
    0
    Location:
    PA
    I tried =P18-Q6. It wouldn't give me a result in the cell, it would just display the formula in the cell.
     

Share This Page