# Question about formulas for the MS-Excel experts among us

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

Joined:
Mar 6, 2000
Posts:
4,389
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. ### Goober1/2 ton status

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

=RIGHT(YEAR(A1),2)&amp;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

Joined:
Mar 6, 2000
Posts:
4,389
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)&amp;TEXT(T18-DATE(YEAR(T18),1,0),"000")

???

4. ### Goober1/2 ton status

Joined:
Apr 26, 2002
Posts:
2,222
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)&amp;TEXT(T18-DATE(YEAR(T18),1,0),"000")

5. ### DPI1/2 ton status

Joined:
Jun 22, 2000
Posts:
2,792
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. ### DPI1/2 ton status

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

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

7. ### Goober1/2 ton status

Joined:
Apr 26, 2002
Posts:
2,222
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

Joined:
Mar 6, 2000
Posts:
4,389
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. ### Stickseler3/4 ton status

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

=P18-Q6

This would give you # of days

10. ### 75-K53/4 ton status

Joined:
Apr 28, 2002
Posts:
5,353
0
Location:
Anderson, Indiana