+ Reply to Thread
Results 1 to 5 of 5

Julian Date Conversion and Formula Modification

  1. #1
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Eagle River, Alaska
    MS-Off Ver
    Excel 2010
    Posts
    145

    Julian Date Conversion and Formula Modification

    I have searched the web for answers but cannot seem to find how to modify these formulas. I have attached a working file. I would like to have Cell B4 represented by just the last three digits (305 instead of 14305).

    Similarly, I would like to INPUT only three digits in Cell A5 and have the date represented in Cell B5. This may be more difficult to do.

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Julian Date Conversion and Formula Modification

    You can use this in B4:

    =TEXT(A4-DATE(YEAR(A4),1,0),"000")

    I'll need to look at B5 in more detail, but it is time for my meal now (my wife is calling me).

    Hope this helps.

    Pete

  3. #3
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Eagle River, Alaska
    MS-Off Ver
    Excel 2010
    Posts
    145

    Re: Julian Date Conversion and Formula Modification

    That nails down the first part! Thanks!!

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Julian Date Conversion and Formula Modification

    For the second part, will you be assuming the current year if you only enter 3 digits in A5? If so, you can use this:

    =DATE(YEAR(TODAY()),1,RIGHT(A5,3))

    Hope this helps.

    Pete

  5. #5
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Eagle River, Alaska
    MS-Off Ver
    Excel 2010
    Posts
    145

    Re: Julian Date Conversion and Formula Modification

    Perfect!!! Just what I was looking for. Many thanks! Chris

+ 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. Julian date formula
    By schnett in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-03-2007, 10:47 AM
  3. 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
  4. Julian conversion
    By S4E in forum Excel General
    Replies: 6
    Last Post: 02-14-2005, 03:39 PM
  5. [SOLVED] WS formula for Julian date not converting well to VBA??
    By Ed in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-10-2005, 02: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