Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

How do I convert dd/mm/yy date format to yyddd Julian date format

387 views
Skip to first unread message

itzy bitzy

unread,
Dec 10, 2009, 2:13:02 PM12/10/09
to
I have done this before, but can't remember how!

David Biddulph

unread,
Dec 10, 2009, 2:22:52 PM12/10/09
to
=TEXT(A1,"yy")&TEXT(A1-DATE(YEAR(A1),1,0),"000")
--
David Biddulph

"itzy bitzy" <itzy...@discussions.microsoft.com> wrote in message
news:D05271B8-8D0F-44D7...@microsoft.com...

Brad Autry

unread,
Dec 10, 2009, 2:23:01 PM12/10/09
to
Format cells > Custom > and in "Type" enter: yyyy dd mm or whatever you'd like

Pete_UK

unread,
Dec 10, 2009, 2:25:55 PM12/10/09
to
Various formulae relating to dates here:

http://www.cpearson.com/excel/jdates.htm

Hope this helps.

Pete

On Dec 10, 7:13 pm, itzy bitzy <itzybi...@discussions.microsoft.com>
wrote:

FrankWood

unread,
Dec 10, 2009, 2:25:02 PM12/10/09
to
If you are trying to get 02/08/1964 to show as 64Sat you could just change to
format of the cell to “Custom” and set it as YYDDD.

If you want to convert it to the PC serial number set the formatting to
“general” and you will get the number of days from Jan 1, 1900 and the date
(assuming you are not using the 1904 date system).

If that’s not what you are looking for give me a little more info. What is
the date in questions and what do you want it to look like as a result.

Hope that helps.

Frank

Eduardo

unread,
Dec 10, 2009, 2:25:02 PM12/10/09
to
Hi,
with custom format, choose the range to be changed, right click on the
mouse, custom, type

yy ddd

Pete_UK

unread,
Dec 10, 2009, 2:35:10 PM12/10/09
to
Julian dates have a 2-digit year number followed by a 3-digit day
number, so they may look something like 09235, meaning the 235th day
of 2009 (century 2000 assumed for yy between 00 and 30).

Follow the link I posted for further details.

Hope this helps.

Pete

> > I have done this before, but can't remember how!- Hide quoted text -
>
> - Show quoted text -

David Biddulph

unread,
Dec 10, 2009, 4:26:20 PM12/10/09
to
But that doesn't give a Julian date, Eduardo.
Today it would give 09Thu, instead of 09344
--
David Biddulph

"Eduardo" <Edu...@discussions.microsoft.com> wrote in message
news:2F8504C4-2DC1-4EF5...@microsoft.com...

Rick Rothstein

unread,
Dec 10, 2009, 10:20:55 PM12/10/09
to
Here is yet another way (one function call less than the other posted
formulas)...

=RIGHT(YEAR(A1)*1000+A1-DATE(YEAR(A1),1,0),5)

And if you are willing to trade a function call for a concatenation, we can
reduce the formula by yet another function call...

=RIGHT(YEAR(A1)*1000+A1-("1/1/"&YEAR(A1))+1,5)

--
Rick (MVP - Excel)


"itzy bitzy" <itzy...@discussions.microsoft.com> wrote in message
news:D05271B8-8D0F-44D7...@microsoft.com...

0 new messages