+ Reply to Thread
Results 1 to 6 of 6

How to convert julian date to standard date

  1. 10-29-2015, 03:23 AM #1
    dolphino
    dolphino is offline
    Forum Contributor
    Join Date
    12-29-2014
    Location
    Indonesia
    MS-Off Ver
    Office 2010, 2013
    Posts
    125

    How to convert julian date to standard date

    hi all

    please kindly help,
    I want to change the julian date to the date of the standard, how to change the formula that date. i'm already try but still error.
    examples of the attached file for this thread.

    thank you in advance. HOW TO CONVERT JULIAN DATE TO STANDARD DATE.xlsx

    Regard
    Dolphino
    Reply With QuoteRegister To Reply
  2. 10-29-2015, 03:30 AM #2
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: How to convert julian date to standard date

    =DATE(LEFT(B4,4)+0,1,MID(B4,5,3)+0) 
    Please Login or Register  to view this content.
    Try this formula
    see attached file
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.
    Reply With QuoteRegister To Reply
  3. 10-29-2015, 03:32 AM #3
    igormigor
    igormigor is offline
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague
    MS-Off Ver
    Excel 2013
    Posts
    160

    Re: How to convert julian date to standard date

    Presuming that the date you want to convert is in cell A4, try with this formula:

    =DATE(LEFT(A4,4),1,0)+RIGHT(A4,3)

    One watch out, the formula will work correctly if the value in cell A4 is in format 2014362, where with first 4 digits is given the year, and with last 3 digits is given the day in the year.
    Reply With QuoteRegister To Reply
  4. 10-29-2015, 03:36 AM #4
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to convert julian date to standard date

    Try this...

    =DATE(LEFT(B4,4),1,0)+MID(B4,5,3)

    Format as Date
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  5. 10-29-2015, 03:36 AM #5
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: How to convert julian date to standard date

    Quote Originally Posted by igormigor View Post
    Presuming that the date you want to convert is in cell A4, try with this formula:

    =DATE(LEFT(A4,4),1,0)+RIGHT(A4,3)

    One watch out, the formula will work correctly if the value in cell A4 is in format 2014362, where with first 4 digits is given the year, and with last 3 digits is given the day in the year.
    what about if A4 is in format 201436
    Reply With QuoteRegister To Reply
  6. 10-29-2015, 04:22 AM #6
    dolphino
    dolphino is offline
    Forum Contributor
    Join Date
    12-29-2014
    Location
    Indonesia
    MS-Off Ver
    Office 2010, 2013
    Posts
    125

    Re: How to convert julian date to standard date

    hi nfsales, igor migor and tony valko

    your formula working verywell, thank you for your help.
    I'm verry happy follow this forum, very help to me.


    Regard
    Dolphino
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Converting a julian date and time to standard date in excel 2010
    By Munkle555 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-22-2015, 10:49 AM
  2. 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
  3. Convert Julian time and year to standard date and time
    By gozo in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 09-11-2014, 06:42 AM
  4. [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
  5. 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
  6. 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
  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

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1