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

(2) Share

Report

Posted on by ShruthiNayak

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)

All responses (6)

Answers (1)

Sort by

Suggested answer

Saif Ali Sabri 2,439 Moderator on at

Like (2)

Report
Copy link

Link copied!
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:

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

Was this reply helpful? Yes No
ShruthiNayak 62 on at

Like (1)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
Suggested answer

Saif Ali Sabri 2,439 Moderator on at

Like (1)

Report
Copy link

Link copied!
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:

Use CONCATENATE instead of +

In ER, + is not supported for string concatenation. Use CONCATENATE() instead.

Ensure Julian Date is properly formatted

TEXT(136, "000") ensures it always has 3 digits (e.g., 136 → 136, 5 → 005).

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.

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)

Was this reply helpful? Yes No
ShruthiNayak 62 on at

Like (1)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
Verified answer

Saif Ali Sabri 2,439 Moderator on at

Like (2)

Report
Copy link

Link copied!
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:

Extract Year and Julian Day:

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

Convert Julian Day to a Date:

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

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:

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

Was this reply helpful? Yes No
Suggested answer

ShruthiNayak 62 on at

Like (1)

Report
Copy link

Link copied!

Thank you, this seems to be working,

Was this reply helpful? Yes No