Update Your Dates & Voucher Numbers With Excel in D365 Finance & Operations

When using the “open lines in Excel” function in Dynamics 365 Finance & Operations, the system provides one voucher number for the entire entry, no matter if you have 10 lines or 1000 lines. That can create a problem for clients who would like to use this feature for uploading credit card transactions from their monthly statement. The problem with one voucher number is the date; it must have the same date. Therefore, this function is not ideal for credit card transactions with several lines and different dates, as all lines will have the same date, instead of the date when the withdrawal happened.

There is no direct solution for this, but there is a workaround in the system so it can be used for credit card transaction uploads with only a few extra steps.

Note: Even though most customers, commentators, and consultants still call it Dynamics 365 Finance & Operations or D365FO, Microsoft now technically licenses it as Dynamics 365 Finance and Supply Chain Management.

To make this work, the following has to be set up in the system: Go to Organization Administration => number sequences => number sequences.

Create a new voucher series: Click “new” and fill it out as shown below, and click “save”. If it involves more than one company, choose “shared” instead of company.

Go to General ledger => Journal setup => Journal names. Click on “new” and fill out the marked fields as shown below, and save it.

Go to General journals => Journal entries => General journals. Click “new” and fill out the fields.

Click on “Open lines in Excel” and choose “General journal line entry” and click “ok”. Download the file, open the file and click on “Enable editing”.

It should look like this with all information in the left corner:

Go to the excel file you would like to use.

You can create columns as needed, but dates and voucher must be next to each other. Create a credit card suspense account 999998 (General ledger => Chart of account => Accounts => Main accounts).

The Excel file should look like this:

The dates are not needed. If there are no dates, it will just use today’s date, but since we are going to change it, there is no reason to copy the dates in.

Click on “publish”. Sometimes there is an error on the vendor line, so you will have to type it into your journal.

Go back to Dynamics 365 Finance & Operations and click on “lines”. Go back to your excel sheet and copy the dates and voucher column.

Copy them into the journal. Click on the first date and click “paste”.

It will take a while to process depending on the numbers of lines, so be patient. Make sure you do not leave the journal while it’s processing. When it starts changing the dates, the dates will be in italics. When it is done updating, and it asks you to save it, the italics will disappear. Then it is time to validate posting and post.

If you have any questions about using Dynamics 365 Finance & Operations, please contact us.