converting a 7-digit julian date to a calendar date

[Guest]

I need to convert a 7-digiti julian date to a calendar date. For example, my cell reads 2004029 but I need to convert it to read 01/29/2004.
Thanks.

 

[David McRitchie]

First of all that is not what is referred to as a Julian date
by astronomers nor [as misused] by programmers.

=DATE(left(A1,4), mid(A1,5,2), right(a1,2))

I have a page on Date and Time
http://www.mvps.org/dmcritchie/excel/datetime.htm
as does Chip Pearson
http://www.cpearson.com/excel/datetime.htm
I'm sure many others have pages on at least some aspect
of date and/or time as well.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[Ron Rosenfeld]

I need to convert a 7-digiti julian date to a calendar date. For example, my cell reads 2004029 but I need to convert it to read 01/29/2004.
Thanks.

=DATE(INT(A1/10^3),1,MOD(A1,INT(A1/10^3)))


--ron

 

[Fred Smith]

Use:

=date(left(a1,4),1,right(a1,3))

One of the great things about the Date function is that it will cross over
month boundaries. So if you ask for the zeroth of a month, it will give you
the last day of the previous month. Similarly, in your case, it you ask for
the 56th of January, it will correctly figure out the 56th day of the year,
or 02/25/1994.

--
Regards,
Fred
Please reply to newsgroup, not e-mail

I need to convert a 7-digiti julian date to a calendar date. For example,

my cell reads 2004029 but I need to convert it to read 01/29/2004.

 

[David McRitchie]

sorry misread it, my eyes inserted another digit. And yes that would
be a Julian date as known to programmers. Also did not pick up
that you were trying to convert the date to show 1st of a month,
because I thought the 1 was already there, but your other responders
were much more observant.

 

[Greg Rivet]

Similar question as above, except I need to convert 1040131 to 1/31/04. TIA

Greg

 

[Ron Rosenfeld]

Similar question as above, except I need to convert 1040131 to 1/31/04. TIA

You'll have to give more information.

"Above" is a Julian date characterized by digits that represent a year, and
then by the number of the day in that year.

I can't relate your number to that paradigm.


--ron

 

[Greg Rivet]

Ron the 1040131 is a date format in access the 104=2004, 01=Jan, 31=day.
HTH.

Greg

 

[Fred Smith]

Ron,

First, you are better off to create a new thread than add to an existing
one. Many people ignore threads with many replies because they assume the
problem has been address.

Second, what you have is a number which must be converted to a date. You do
that with three formulas, one for year, one for month, one for day.

The year is easy: =int(a1/10000)+1900
The day is easy: =mod(a1,100)
The month is more complicated because you have to use both -- first get rid
of the year, then get rid of the day: =int(mod(a1,10000))/100

You combine this with:

=date(int(a1/10000)+1900,int(mod(a1,10000))/100,mod(a1,100))

--
Regards,
Fred
Please reply to newsgroup, not e-mail

Ron the 1040131 is a date format in access the 104=2004, 01=Jan, 31=day.
HTH.

Greg

 

[Ron Rosenfeld]

Ron,

First, you are better off to create a new thread than add to an existing
one. Many people ignore threads with many replies because they assume the
problem has been address.

Perhaps you should address your complaint to the poster who asked the question,
rather than to the respondent. Had I started a new thread with my response,
the party that asked the question would have had a good deal of difficulty
finding an answer.

Second, what you have is a number which must be converted to a date. You do
that with three formulas, one for year, one for month, one for day.

The year is easy: =int(a1/10000)+1900
The day is easy: =mod(a1,100)
The month is more complicated because you have to use both -- first get rid
of the year, then get rid of the day: =int(mod(a1,10000))/100

You combine this with:

=date(int(a1/10000)+1900,int(mod(a1,10000))/100,mod(a1,100))

--ron

 

[Peo Sjoblom]

Perhaps you should address your complaint to the poster who asked the question,
rather than to the respondent. Had I started a new thread with my response,
the party that asked the question would have had a good deal of difficulty
finding an answer.

LOL!

 

[Ron Rosenfeld]

Ron the 1040131 is a date format in access the 104=2004, 01=Jan, 31=day.
HTH.

Ok, that makes it simple. It's not really a Julian date.

I will guess that, for consistency 1/31/1999 would be represented as 990131.

That being the case, the conversion formula is:

=DATE(INT(A1/10^4),MOD(INT(A1/100),100),MOD(A1,100))


--ron

 

[Fred Smith]

Sorry about that. "Ron" should have been "Greg".

--
Regards,
Fred
Please reply to newsgroup, not e-mail

 

[Greg Rivet]

Thank you Fred

Greg

Ron,

First, you are better off to create a new thread than add to an existing
one. Many people ignore threads with many replies because they assume the
problem has been address.

Second, what you have is a number which must be converted to a date. You do
that with three formulas, one for year, one for month, one for day.

The year is easy: =int(a1/10000)+1900
The day is easy: =mod(a1,100)
The month is more complicated because you have to use both -- first get rid
of the year, then get rid of the day: =int(mod(a1,10000))/100

You combine this with:

=date(int(a1/10000)+1900,int(mod(a1,10000))/100,mod(a1,100))