Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 6th 09, 04:25 PM posted to microsoft.public.excel.misc
Raj Raj is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 130
Default convert Julian date to DD/MM/YYYY

Hi
I want to convert Julian Date to DD/MM/YYYY format is there any way we can
do it in excel

  #2   Report Post  
Old January 6th 09, 04:44 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,440
Default convert Julian date to DD/MM/YYYY

Look he

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Raj" wrote in message
...
Hi
I want to convert Julian Date to DD/MM/YYYY format is there any way we can
do it in excel


  #3   Report Post  
Old January 6th 09, 05:04 PM posted to microsoft.public.excel.misc
Raj Raj is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 130
Default convert Julian date to DD/MM/YYYY

Hi neik
I tried the formula
=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1 ,RIGHT(A1,3))
I have the julian date as 108001 has to give 1/1/2008 but it is giving
1/1/2010 can you help me regarding this
"Niek Otten" wrote:

Look he

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Raj" wrote in message
...
Hi
I want to convert Julian Date to DD/MM/YYYY format is there any way we can
do it in excel


  #4   Report Post  
Old January 6th 09, 05:26 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,440
Default convert Julian date to DD/MM/YYYY

108001 is not a Julian date as defined by Chip; it has 6 digits, not 5.
What is the meaning of the first 1?
If you don't need it, drop it and use Chip's formula.
Otherwise, tell us what the coding system of your dates is.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Raj" wrote in message
...
Hi neik
I tried the formula
=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1 ,RIGHT(A1,3))
I have the julian date as 108001 has to give 1/1/2008 but it is giving
1/1/2010 can you help me regarding this
"Niek Otten" wrote:

Look he

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Raj" wrote in message
...
Hi
I want to convert Julian Date to DD/MM/YYYY format is there any way we
can
do it in excel



  #5   Report Post  
Old January 6th 09, 05:46 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,440
Default convert Julian date to DD/MM/YYYY

<what the coding system of your dates is

Look here to see how many interpretations of "Julian date"there a

http://en.wikipedia.org/wiki/Julian_date

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Niek Otten" wrote in message
...
108001 is not a Julian date as defined by Chip; it has 6 digits, not 5.
What is the meaning of the first 1?
If you don't need it, drop it and use Chip's formula.
Otherwise, tell us what the coding system of your dates is.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Raj" wrote in message
...
Hi neik
I tried the formula
=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1 ,RIGHT(A1,3))
I have the julian date as 108001 has to give 1/1/2008 but it is giving
1/1/2010 can you help me regarding this
"Niek Otten" wrote:

Look he

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Raj" wrote in message
...
Hi
I want to convert Julian Date to DD/MM/YYYY format is there any way we
can
do it in excel





  #6   Report Post  
Old January 6th 09, 06:27 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 857
Default convert Julian date to DD/MM/YYYY

Hi,

Please explain the 108001 date to us so we can help figure out what you
need. Where does the 80 come from? 10 is 2010? and 01 is 1?

Cheers,
Shane Devenshire

"Raj" wrote in message
...
Hi neik
I tried the formula
=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1 ,RIGHT(A1,3))
I have the julian date as 108001 has to give 1/1/2008 but it is giving
1/1/2010 can you help me regarding this
"Niek Otten" wrote:

Look he

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Raj" wrote in message
...
Hi
I want to convert Julian Date to DD/MM/YYYY format is there any way we
can
do it in excel


  #7   Report Post  
Old January 6th 09, 08:09 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: May 2008
Posts: 44
Default convert Julian date to DD/MM/YYYY

=DATE(INT(A1/1000)-1,12,31)+(-(INT(A1/1000)*1000)+A1)

The above seems to work for me. Excel seems to assume the first three
digits should be added to 1900.

"Raj" wrote:

Hi
I want to convert Julian Date to DD/MM/YYYY format is there any way we can
do it in excel

  #8   Report Post  
Old January 6th 09, 08:26 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 8,651
Default convert Julian date to DD/MM/YYYY

Was there any special reason for using
+(-(INT(A1/1000)*1000)+A1)
rather than
+MOD(A1,1000) ?
--
David Biddulph

AnotherNewGuy wrote:
=DATE(INT(A1/1000)-1,12,31)+(-(INT(A1/1000)*1000)+A1)

The above seems to work for me. Excel seems to assume the first three
digits should be added to 1900.

"Raj" wrote:

Hi
I want to convert Julian Date to DD/MM/YYYY format is there any way
we can do it in excel



  #9   Report Post  
Old January 6th 09, 08:40 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: May 2008
Posts: 44
Default convert Julian date to DD/MM/YYYY

Because I didn't think of using mod() ;o)

I worked this out two years ago to deal with Julian dates formatted like
2008001. I've learned a lot since then, primarily from this forum. It
turned out the exact formula worked for dates formatted like 108001.

thx for the suggestion. I'll change my example sheet.

"David Biddulph" wrote:

Was there any special reason for using
+(-(INT(A1/1000)*1000)+A1)
rather than
+MOD(A1,1000) ?
--
David Biddulph

AnotherNewGuy wrote:
=DATE(INT(A1/1000)-1,12,31)+(-(INT(A1/1000)*1000)+A1)

The above seems to work for me. Excel seems to assume the first three
digits should be added to 1900.

"Raj" wrote:

Hi
I want to convert Julian Date to DD/MM/YYYY format is there any way
we can do it in excel






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
how to convert from julian date to mm/dd/year robin watersong Excel Discussion (Misc queries) 6 September 9th 07 04:18 AM
convert date mm/dd/yyyy to dd/mm/yyyy maryj Excel Worksheet Functions 2 March 20th 07 07:38 PM
to convert a julian date back to regular date Lynn Hanna Excel Worksheet Functions 1 July 26th 06 03:14 PM
Convert a julian gregorian date code into a regular date Robert Excel Worksheet Functions 3 June 13th 06 07:03 PM
convert Julian date Doug Excel Worksheet Functions 3 May 5th 05 07:30 PM


All times are GMT +1. The time now is 11:12 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017