+ Reply to Thread
Results 1 to 12 of 12

Formula for a slightly different Julian layout - convert to date.

  1. #1
    Registered User
    Join Date
    11-15-2013
    Location
    Newquay, Cornwall
    MS-Off Ver
    Excel 2010
    Posts
    67

    Formula for a slightly different Julian layout - convert to date.

    Hello all,

    Where I work we use a slightly different version of the Julian Calendar. We have the first 3 digits as the day, the fourth digit is the year, and the last digit is the Production Line the work was produced on. Can anyone show me a formula for converting this into a regular DD/MM/Year format?

    For example:

    The first 3 numbers are the Julian day, the fourth number is the year (5=2015) and the last number is the Production Line

    22358 - Produced on day 12/8/2015 on Line 8.

    Consequently the Julian date would sometimes have a 0 in front of it, so would the format of the column need to be something special, or can the formula take into account leading zero's?

    Thanks in advance!
    Still learning... one day I will be able to help!

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Formula for a slightly different Julian layout - convert to date.

    If you want leading zero, format cell as "00000"

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Formula for a slightly different Julian layout - convert to date.

    Hi,

    Does
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    work for you.

    I'm not sure what you mean when you say there would sometimes be a zero in front of it. Would you offer an example and indicate what that should produce.

    You haven't said what would happen in the next decade. How would you represent the year 2025 and differentiate it from the current '5' which represents 2015.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    11-15-2013
    Location
    Newquay, Cornwall
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Formula for a slightly different Julian layout - convert to date.

    Thanks John, thought it might be something like that.

    Richard - That's pretty close. It's currently one day out. Is that something to do with leap years, or something else? For example on day 279 this year is 06/10/2015, but your formula is coming out with 07/10/2015. Does there need to be a slight modification in there?

    With regard to the zero's I talking about day 001, 002, 015 etc, etc. That should be sorted with the formatting mentioned above.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Formula for a slightly different Julian layout - convert to date.

    =DATE(2010+MID(A1,4,1),1,LEFT(A1,3))
    will do it.
    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

  6. #6
    Registered User
    Join Date
    11-15-2013
    Location
    Newquay, Cornwall
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Formula for a slightly different Julian layout - convert to date.

    Thanks Glen, that seems to do the trick, much appreciated.

    With regard to the next decade.... well I'm not sure who set thuis system up, and whether they thought that far ahead, but it will just flip the forth number around to 0,1,2 again. So I might need to modify the formula in 5 years time or so!

    Reps added. Thanks everyone.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Formula for a slightly different Julian layout - convert to date.

    You're welcome...

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Formula for a slightly different Julian layout - convert to date.

    Quote Originally Posted by Graham Pall View Post
    Thanks John, thought it might be something like that.

    Richard - That's pretty close. It's currently one day out. Is that something to do with leap years, or something else?
    No, it's simply that I left off a final "-1"
    Should have been
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    11-15-2013
    Location
    Newquay, Cornwall
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Formula for a slightly different Julian layout - convert to date.

    Ok chaps, slight hiccup, not urgent for this very moment, but would like to get it sorted.

    The column is special formatted as 00000. If I put a date in with two leading zero's (eg 00156 - 1st Jan 2015, Line 6), the formula returns '#VALUE' rather than the date. If the Julian date has one leading zero (eg 01256 - 12th Jan 2015, Line 6) the formula returns 04/05/2016, presumably because the formula is ignoring the leading zero, and picking up Day 125 - 2016.

    Is there any way around this with our weird Julian date system?

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Formula for a slightly different Julian layout - convert to date.

    No problem. It's just me not really reading all of your post:


    =DATE(2010+MID(TEXT(A1,"00000"),4,1),1,LEFT(TEXT(A1,"00000"),3))

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Formula for a slightly different Julian layout - convert to date.

    Testing suggests the field should be formatted as TEXT (rather than "00000" which treats input as 156) i.e. 00156 (TEXT)
    date of 1/1/15


    Glen has addressed this with his reply

  12. #12
    Registered User
    Join Date
    11-15-2013
    Location
    Newquay, Cornwall
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Formula for a slightly different Julian layout - convert to date.

    Cool. That works. Thanks again chaps!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. convert calendar date ddmmyy to julian date yyyydd
    By imichalopo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-13-2015, 01:47 AM
  2. [SOLVED] How to convert Julian Date to regular excel date
    By Drehb4life in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-19-2013, 10:25 AM
  3. Excel 2007 : UDF to Convert Conventional to Julian Date
    By cuznjames51 in forum Excel General
    Replies: 1
    Last Post: 08-19-2011, 11:15 AM
  4. [SOLVED] to convert a julian date back to regular date
    By Lynn Hanna in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-26-2006, 10:20 AM
  5. Convert a julian gregorian date code into a regular date
    By Robert in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-13-2006, 02:10 PM
  6. how to convert julian date to general date
    By arkprabha in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-03-2006, 05:25 AM
  7. how to convert julian date to regular calendar date
    By Ron in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-05-2005, 07:06 PM
  8. [SOLVED] convert Julian date
    By Doug in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-05-2005, 03:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1