Jullian Time conversion
I have a program that I have exported some data from for analysis in Excel, The problem is that the program exports the time in Julian format but when using it shows it in GMT, I have outlined below examples of this, Is it possible to get a formula to resolve this?
Julian Time GMT
34937 09:41:00
39104 10:49:00
40703 11:17:00
45728 12:41:00
42957 11:55:00
52982 09:35:00
39812 12:07:00
37525 14:29:00
You never said what format you wanted it in, but if you want a simple D/M/YYYY format:You change the text to whatever format you want.
Please Login or Register to view this content.
This is actually a time and not a date and I want it in HH:MM if possible
Please try the following formula-
=1*RIGHT(A1,8)
I can't offer a solution that's better than cbatrody... but Hi there from elsewhere in NI !!
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Firstly Glenn Hi, Nice to see another NI member, Secondly cbatrody I have tried the formula but when 34937 is entered I still get 34937 as the returned value?
I'm offline (on the phone); but did you format the calls as hh:mm? It looked good to me...
Please see the attached example file, it works if the Date & Time you mentioned in your example is in one column.
Sorry my mistake, the example is meant to be 2 columns, the one on the left being the jullian time and the one on the right being the actual time so ot is the number on the left that needs converted to the one on the right. Hope this makes things a bit clearer
Please see the attached file, I have converted the numbers on the left side into decimals and changed the format to hh:mm:ss. I am not sure if this is the right way to convert Julian Time.
julian time is the time of the day before at mid day + the fraction of a day
so
=A1/100000+0.5 should convert it
50000 =midnight
75000 = 06:00
25000= 18:00
00000 = midday
now of course the times wont be in order
as anything over 50000 will be later than anything before 50000
so
75000 = 06:00
is actually later than
25000= 18:00
because the 06:00 is the next day
Last edited by martindwilson; 08-07-2014 at 04:05 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Ive tried that and now i'm getting 20:23 out of 34937 when the program I am using is saying it is actually 09:41, any ideas?
34937 is less than 50000 so it must be a time between midday and mid night
look here
http://www.onlineconversion.com/julian_date.htm
see the bit julian date
2456876.91829
it currently shows the time as ut 10h 2min 20 seconds
change it to
2456876.34937 then calculate
the time displays as 20 hrs 23 mins and 6 seconds
then its not julian time its maybe unix time (=A1 / 86400 + "1/1/1970")
but even then this doesn't agree with what you have
------------------unix time
34937 09:41:00 9:42
39104 10:49:00 10:51
40703 11:17:00 11:18
45728 12:41:00 12:42
42957 11:55:00 11:55
52982 09:35:00 14:43
39812 12:07:00 11:03
37525 14:29:00 10:25
The company that engineer the software that I am using assures me that it is Jullian time but I cant find anything anywhere that compares to what I have, what other time formats are there that this could be?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks