difference between julian dates

[Jon Savage]

Hello there,
I would first off like to thank ya for the great info that you have available on your site and that I am always amazed at some of the solutions that you come with. Been a great help to me many a times.

I have been trying to figure out a way to get the total time difference (in hour and minutes) between two julian dates and times. For example, a program that I use at work uses a "julian" date of "4032 1630" to equal 1 feb 04 at 4:30 pm. So I'm needing to be able to find the total hours and minutes difference between say "4032 1630" and "4048 2214". I figure that this must be easy to do using the date and time as a serial number then format the difference as a hour and minute but I'm at lost how to do this . Any way that you can assist me on this would be greatly appreciated. Thanks again, Jon

 

[just_jon]

Welcome to the Board --

I'd recommend a look at Chip Pearson's write-up here -- http://www.cpearson.com/excel/jdates.htm -- as better than anything I could ever offer.

 

[AlohaJulio]

If the time you are looking to convert is in A1, you can try:
=37987+MID(A1,2,3)+(MID(A1,6,2)&":"&RIGHT(A1,2)&":00")

The 37987 is for Jan 1, 2004, the Mid function adds the number of days to 1/1/04, the Mid and right concatenations turns the time into 00:00:00 format which is then added to the date.

Once you convert the two dates, getting the difference is just a matter of subtraction.

A little klugy pehaps, but...

 

[IML]

I was just hacking away at this a bit before the good advice to see chip pearson's site. If I got your format right, with your Julian date/time in a1, use
=DATE(2000+LEFT(A1),1,--MID(A1,2,3))+TEXT(RIGHT(A1,4),"00\:00")

to turn it an "excel" date time that can be further manipulated with subtraction, etc. This has Y3K bug built in, so beware.

 

[just_jon]

That's slick, IML.

 

[IML]

I like Aloho's as well. The only limitation is it doesn't know what year it is. You could also right his/her as (adjusted the constant to 12/31/03)
=SUM(37986,MID(A1,{2,6,8},{3,2,2})/{1,24,1440})

 

[Jon Savage]

WOW that was quick with the responses. Thanks for all the suggestions. I managed to get the difference between the two date/times and multiplied by 24 to get 390.2333333 hours. How can I convert the .2333333 to minutes? Thanks again for all the help. Jon

 

[PaddyD]

=TIME(INT(A1),MOD(A1,1)*60,0)

 

[Jon Savage]

Well, I still get this to work right. I have the two serial numbers for the dates and time and in the above example I subtracted the two serials to equal 16.25972222 which I believe means 16 days and .259... of 24 hours. So multiplying that by 24 equals 390.2333333 hours But I can't figure out how to get a return of xxxhours and xx minutes (HHH:MM or something like). Sure it's something simple. Didn't see anything at Chip Pearson site but heading back over there to do some more digging. Tried the TIME function above but...well... I guess I'm not the sharpest knife in the drawer . any suggestions?? Thanks again for your time Jon

 

[PaddyD]

trivial option - format the cell with the result as [hh]:mm - should give you 390:14

also, i was reading 16.259722 as hours.minutes - if this is actually days.minutes then try:

=TEXT(A10,"[hh]:MM")+0