Page 1 of 2 12 LastLast
Results 1 to 10 of 12

difference between julian dates

This is a discussion on difference between julian dates within the Excel Questions forums, part of the Question Forums category; Hello there, I would first off like to thank ya for the great info that you have available on your ...

  1. #1
    New Member
    Join Date
    Mar 2004
    Posts
    4

    Default difference between julian dates

    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

  2. #2
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default Re: difference between julian dates

    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.
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  3. #3
    Board Regular
    Join Date
    Feb 2004
    Location
    Andover, MA
    Posts
    188

    Default Re: difference between julian dates

    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...
    Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy.
    --Albert Einstein

  4. #4
    IML
    IML is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,744

    Default Re: difference between julian dates

    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.

  5. #5
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default Re: difference between julian dates

    That's slick, IML.
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  6. #6
    IML
    IML is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,744

    Default Re: difference between julian dates

    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})

  7. #7
    New Member
    Join Date
    Mar 2004
    Posts
    4

    Default Re: difference between julian dates

    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

  8. #8
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default

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

  9. #9
    New Member
    Join Date
    Mar 2004
    Posts
    4

    Default Re: difference between julian dates

    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

  10. #10
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default Re: difference between julian dates

    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

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com