+ Reply to Thread
Results 1 to 3 of 3

Using Dates to get UNIQUE Julian date

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    12-14-2005
    Location
    US
    MS-Off Ver
    Excel 2019, Excel 365, Excel Mobile (Android)
    Posts
    193

    Using Dates to get UNIQUE Julian date

    I am needing a formula to generate a unique Julian date. The unique date is a combination of the Julian date, the fiscal year and a reference identifier. For example: Z994284.

    Z99 is the reference identifier
    4 is the fiscal year (24) that starts on October 1st. So, the fiscal year is now 2024 not 2023.
    284 is the Julian date which is the number of day since Jan 01.

    What I am hoping for is a formula which will return the correct unique date based on today's date.

    Hope someone can help in this challenge. Thank you for any any help offered.

  2. #2
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: Using Dates to get UNIQUE Julian date

    This formula fits your description, but please note that this date jumps back on January 1:
    Formula: copy to clipboard
    ="Z99"&MOD(YEAR(A2+92),10)&TEXT(A2-DATE(YEAR(A2),1,0),"000")

    That is why I have also drawn up an alternative formula, in which this does not happen:
    Formula: copy to clipboard
    ="Z99"&MOD(YEAR(A12+92),10)&TEXT(A12-DATE(YEAR(A12+92)-1,10,0),"000")
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    12-14-2005
    Location
    US
    MS-Off Ver
    Excel 2019, Excel 365, Excel Mobile (Android)
    Posts
    193

    Re: Using Dates to get UNIQUE Julian date

    Thank you Hans!! The first formula is the correct one. The second formula resets the Julian date on 10/1 to 001 which is not correct. Julian date 001 is at the start of a new calendar year 01/01/2024.

+ 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. Converting julian dates to regular dates
    By sonyat810 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-14-2018, 10:32 PM
  2. Determining the date from lot codes written in Perpetual Julian Calendar Dates
    By Anesu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-12-2013, 10:54 AM
  3. Converting 2010 Julian dates to calender date
    By krie2509 in forum Excel General
    Replies: 8
    Last Post: 02-03-2010, 06:36 PM
  4. Julian dates
    By nick in forum Excel General
    Replies: 3
    Last Post: 05-08-2006, 11:55 AM
  5. Convert Julian dates back to date data type format?
    By Lara in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-23-2006, 10:25 PM
  6. julian dates
    By Lylesm in forum Excel General
    Replies: 5
    Last Post: 01-05-2006, 03:50 PM
  7. [SOLVED] Toggle a range of Julian dates to Gregorian Dates and Back
    By PSKelligan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-18-2005, 10:06 AM

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