When using the interface to transfer data from SAP Financial Consolidation application to Disclosure Management (see here for more details), you may face some issues with date conversion
First, let us define a simple schedule in Financial Consolidation that uses some date fields.
The example uses dd/mm/yy, dd/mm/yyyy, mm/dd/yy, mm/dd/yyyy and the dates used are May, 23rd and May 8th (for ambiguity)
Note that for the format, we are using explicit formats (which is recommended for the FC-DM interface)
To avoid confusion, avoid formats like ">ENV(RUNDATE)[sdate]" as these may appear differently for each user, depending on their regional settings. When running the task, however, the format used depends on the regional settings on the FC application server.
In the interface, Financial Consolidation will generate a .CSV that will be uploaded to DM (via web-services)
Within this CSV, all the date fields are created as text strings, and no conversion takes place.
Note: this is technically a bug in Financial Consolidation, but this will not be fixed.
Once the data has been received in Disclosure Management...
....you can see in the Excel chapter that the data is not formatted (i.e. the cells all have "General" format)
On the values where the YY format was used, Excel will prompt for the YYYY conversion
As you may need real date values (for example, for XBRL tagging), you need to convert the cells.
If you convert the cells with the Excel formula DATEVALUE() (and use the 'date" format on the cells), you will see this
As 23/05/18 cannot be converted, it is better to use the following formula for short dates (using YY)
Note: you may need to adapt the “dd/mm/yy” date in string format into a “mm/dd/yyyy” date in string format by using this formula:
For a more detailed overview of the FC-FM interface, you can check this link.
First, let us define a simple schedule in Financial Consolidation that uses some date fields.
The example uses dd/mm/yy, dd/mm/yyyy, mm/dd/yy, mm/dd/yyyy and the dates used are May, 23rd and May 8th (for ambiguity)
Note that for the format, we are using explicit formats (which is recommended for the FC-DM interface)
To avoid confusion, avoid formats like ">ENV(RUNDATE)[sdate]" as these may appear differently for each user, depending on their regional settings. When running the task, however, the format used depends on the regional settings on the FC application server.
In the interface, Financial Consolidation will generate a .CSV that will be uploaded to DM (via web-services)
Within this CSV, all the date fields are created as text strings, and no conversion takes place.
Note: this is technically a bug in Financial Consolidation, but this will not be fixed.
Once the data has been received in Disclosure Management...
....you can see in the Excel chapter that the data is not formatted (i.e. the cells all have "General" format)
On the values where the YY format was used, Excel will prompt for the YYYY conversion
As you may need real date values (for example, for XBRL tagging), you need to convert the cells.
If you convert the cells with the Excel formula DATEVALUE() (and use the 'date" format on the cells), you will see this
As 23/05/18 cannot be converted, it is better to use the following formula for short dates (using YY)
=IFERROR(DATE(IF(INT(RIGHT(fcdate,2))>50,RIGHT(fcdate,2),INT(RIGHT(fcdate,2))+2000),MID(fcdate,4,2),LEFT(fcdate,2)),"")
Note: you may need to adapt the “dd/mm/yy” date in string format into a “mm/dd/yyyy” date in string format by using this formula:
=IFERROR(CONCATENATE(MID(fcdate,4,2),"/",LEFT(fcdate,2),"/",IF(INT(RIGHT(fcdate,2))>50,INT(RIGHT(fcdate,2))+1900,INT(RIGHT(fcdate,2))+2000)),"")For a more detailed overview of the FC-FM interface, you can check this link.
- SAP Managed Tags:
