web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

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

(2) 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
I have the same question (0)
  • Suggested answer
    Saif Ali Sabri Profile Picture
    Saif Ali Sabri 2,351 Super User 2025 Season 2 on at

    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! 🚀

  • ShruthiNayak Profile Picture
    ShruthiNayak 40 on at
    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 2,351 Super User 2025 Season 2 on at
    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
    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?
     
  • Verified answer
    Saif Ali Sabri Profile Picture
    Saif Ali Sabri 2,351 Super User 2025 Season 2 on at

    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! 🚀

  • Suggested answer
    ShruthiNayak Profile Picture
    ShruthiNayak 40 on at
    Thank you, this seems to be working, 

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

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 592 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 478 Super User 2025 Season 2

#3
BillurSamdancioglu Profile Picture

BillurSamdancioglu 305 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans