Skip to main content

Notifications

Announcements

🌸 Community Spring Festival 2025 Challenge & Updates🌸
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Converting Julian date...
Finance | Project Operations, Human Resources, ...
Answered

Converting Julian date to UK date format in Electronic reporting while importing bank statement

(3) ShareShare
ReportReport
Posted on by ShruthiNayak 40
Converting Julian date to UK date format in Electronic reporting while importing bank statement. Need help to convert Julian date (136,  into DDMM -1605)format to import into dynamics as normal ddmm format as part of bank statement import.
Categories:
Dynamics 365 Finance
  • Suggested answer
    ShruthiNayak Profile Picture
    ShruthiNayak 40 on at
    Converting Julian date to UK date format in Electronic reporting while importing bank statement
    Thank you, this seems to be working, 
  • Verified answer
    Saif Ali Sabri Profile Picture
    Saif Ali Sabri 1,837 Super User 2025 Season 1 on at
    Converting Julian date to UK date format in Electronic reporting while importing bank statement

    Got it! Since the DATEVALUE function in Electronic Reporting (ER) does not directly support Julian date formats, we need to take a different approach. 

    Solution Approach: 

    1. Extract Year and Julian Day: 

    1. Julian Date 25080 → Year: 2025, Julian Day: 080. 

    1. Convert Julian Day to a Date: 

    1. Add the Julian Day to 1st January of that year. 

    1. Format the Date in UK Format (DDMM). 

     

    Final Formula in Electronic Reporting (ER): 

    er 

    CopyEdit 

    TEXT(ADDDAYS(DATEVALUE(CONCATENATE(TEXT(25, "00"), "0101"), "yyyyMMdd"), VALUE(TEXT(080, "000")) - 1), "ddMM") 

     

    Breakdown of the Formula: 

    1. Get the Year (First Two Digits of Julian Date): 

    er 

    CopyEdit 

    TEXT(25, "00")  → "25"  (for 2025) 

    • Assumes the Julian date format is YYDDD (like 25080 for 21-Mar-2025). 

    • Converts "25" to "2025". 

    • Create 1st January of the Year: 

    er 

    CopyEdit 

    DATEVALUE(CONCATENATE(TEXT(25, "00"), "0101"), "yyyyMMdd") 

    • Results in 01-Jan-2025. 

    • Extract Julian Day (Last Three Digits of Julian Date): 

    er 

    CopyEdit 

    VALUE(TEXT(080, "000")) - 1 

    • Converts "080" to 80 and subtracts 1 (since 01-Jan is Day 1). 

    • Add Days to 1st January to Get the Actual Date: 

    er 

    CopyEdit 

    ADDDAYS(DATEVALUE(CONCATENATE(TEXT(25, "00"), "0101"), "yyyyMMdd"), VALUE(TEXT(080, "000")) - 1) 

    • 01-Jan-2025 + 79 days → 21-Mar-2025. 

    • Format the Date as DDMM (UK Format): 

    er 

    CopyEdit 

    TEXT(..., "ddMM") 

    • Converts 21-Mar-2025 to "2103".  

      Example Inputs & Outputs:

      Julian Date (YYDDD) Converted Date (DDMM)
      25080 2103 (21-Mar-2025)
      24136 1505 (15-May-2024)
      23100 1004 (10-Apr-2023)

      This should now correctly convert Julian dates to UK format in Electronic Reporting. Let me know if you need adjustments! 🚀

  • ShruthiNayak Profile Picture
    ShruthiNayak 40 on at
    Converting Julian date to UK date format in Electronic reporting while importing bank statement
    Hi Saif,
     
    Thanks for getting back, unfortunately this is  not solving it as well, please see the error below.
     
    I tried same formula for in string as well as Date type field, and getting same error, main issue is around DATEVALUE function, it does not recognise the format. Can you help me to write somethingline like 
     
    1. First convert from Day of the year to format of adding the year prefix (5 digit Julian date example : 21/03/2025 as 25080, and from this convert into UK format?
     
  • Suggested answer
    Saif Ali Sabri Profile Picture
    Saif Ali Sabri 1,837 Super User 2025 Season 1 on at
    Converting Julian date to UK date format in Electronic reporting while importing bank statement
    Thanks for sharing the screenshot! The errors indicate issues with string concatenation (+ operator) and DATEVALUE not accepting the input format.
    Fix:
    Try the following corrected formula in Electronic Reporting (ER):
    er
    CopyEdit
    DATEVALUE(CONCATENATE("2024", TEXT(136, "000")), "yyyyDDD")
    Explanation:
    1. Use CONCATENATE instead of +
      • In ER, + is not supported for string concatenation. Use CONCATENATE() instead.
    2. Ensure Julian Date is properly formatted
      • TEXT(136, "000") ensures it always has 3 digits (e.g., 136 → 136, 5 → 005).
    3. Convert Julian Date to a proper date
      • DATEVALUE(CONCATENATE("2024", TEXT(136, "000")), "yyyyDDD")
      • This converts Julian day 136 of 2024 to 16-May-2024.
    4. Format as DDMM (UK format)
    er
    CopyEdit
    TEXT(DATEVALUE(CONCATENATE("2024", TEXT(136, "000")), "yyyyDDD"), "ddMM")
      • This extracts DDMM format (e.g., 1605 for 16th May).
    Expected Output:
    • Input: 136
    • Output: 1605 (16-May-2024)
  • ShruthiNayak Profile Picture
    ShruthiNayak 40 on at
    Converting Julian date to UK date format in Electronic reporting while importing bank statement
    Hi Saif, 
     
    Thank you for your response, unfortunately I am getting the below error when I try to  define formula. Could you please help me here?
     
  • Suggested answer
    Saif Ali Sabri Profile Picture
    Saif Ali Sabri 1,837 Super User 2025 Season 1 on at
    Converting Julian date to UK date format in Electronic reporting while importing bank statement

    In Electronic Reporting (ER), use the DATEVALUE function to convert a Julian date (e.g., 136) into a standard DDMM (UK format). Here's a concise solution:

    Steps:

    1. Convert Julian Date to Actual Date:

      er
      DATEVALUE("2024" + TEXT(136, "000"), "yyyyDDD")

      • This assumes 2024 as the year. Replace it dynamically if needed.  

    Final Output Example:

    • Input: 136
    • Output: 1605 (16th May)

    This will properly format and import the date into Dynamics 365 during bank statement processing. Let me know if you need further adjustments! 🚀

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

🌸 Community Spring Festival 2025 Challenge & Updates🌸

Quick Links

🌸 Community Spring Festival 2025 Challenge 🌸

WIN Power Platform Community Conference 2025 tickets!

Jonas ”Jones” Melgaard – Community Spotlight

We are honored to recognize Jonas "Jones" Melgaard as our April 2025…

Kudos to the March Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 294,120 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 232,871 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,158 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans