Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default How do I convert dd/mm/yy date format to yyddd Julian date format

I have done this before, but can't remember how!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default How do I convert dd/mm/yy date format to yyddd Julian date format

=TEXT(A1,"yy")&TEXT(A1-DATE(YEAR(A1),1,0),"000")
--
David Biddulph

"itzy bitzy" wrote in message
...
I have done this before, but can't remember how!



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 34
Default How do I convert dd/mm/yy date format to yyddd Julian date format

Format cells Custom and in "Type" enter: yyyy dd mm or whatever you'd like

"itzy bitzy" wrote:

I have done this before, but can't remember how!

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,276
Default How do I convert dd/mm/yy date format to yyddd Julian date format

Hi,
with custom format, choose the range to be changed, right click on the
mouse, custom, type

yy ddd

"itzy bitzy" wrote:

I have done this before, but can't remember how!

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 21
Default How do I convert dd/mm/yy date format to yyddd Julian date format

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 thats 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


"itzy bitzy" wrote:

I have done this before, but can't remember how!



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default How do I convert dd/mm/yy date format to yyddd Julian date format

Various formulae relating to dates he

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

Hope this helps.

Pete

On Dec 10, 7:13*pm, itzy bitzy
wrote:
I have done this before, but can't remember how!


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default How do I convert dd/mm/yy date format to yyddd Julian date format

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

On Dec 10, 7:25*pm, FrankWood wrote:
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



"itzy bitzy" wrote:
I have done this before, but can't remember how!- Hide quoted text -


- Show quoted text -


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default How do I convert dd/mm/yy date format to yyddd Julian date format

But that doesn't give a Julian date, Eduardo.
Today it would give 09Thu, instead of 09344
--
David Biddulph

"Eduardo" wrote in message
...
Hi,
with custom format, choose the range to be changed, right click on the
mouse, custom, type

yy ddd

"itzy bitzy" wrote:

I have done this before, but can't remember how!



  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default How do I convert dd/mm/yy date format to yyddd Julian date format

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" wrote in message
...
I have done this before, but can't remember how!


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
change date format dd/mm/yyyy to Julian date format? itzy bitzy Excel Worksheet Functions 1 December 8th 09 08:42 PM
convert Julian Date Format to Excel Pablo Excel Discussion (Misc queries) 1 January 15th 08 02:14 AM
convert julian date format to excel Pablo Excel Discussion (Misc queries) 1 January 14th 08 11:44 PM
convert Julian Date Format to Excel Pablo Excel Discussion (Misc queries) 2 January 14th 08 09:40 PM
Change satellite julian date format YYYYDDDHHMMSS to excel date ti putley Excel Discussion (Misc queries) 1 January 11th 08 07:12 PM


All times are GMT +1. The time now is 10:25 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"