Linked data from excel not correct format

Question

After multiple people have updated their individual Excel workbooks, I have to generate a report from those multiple excel workbooks. Instead of going into each workbook and copying and pasting their data into my report, I tried to link to the data in excel from access. That way whenever they make their update at the end of the month, I just run the report in Access (btw, I'm not sure if this is the most efficient way to do this). The data I need resides in about 20 cells in the worksheet. I didn't want to see the data for the entire worksheet, so I created named ranges, so I didn't have to link to the entire worksheet, just the data in that worksheet that I needed. Everything worked well, except one cell came back with a different format. In the Excel workbook, the data is a date and formatted as a date. However, when I run the report in Access, it comes back as a serial number, not the date. The date in excel is 12/1/2020, but it instead displays $44,166. I looked in Excel and the cell is formatted in a date format. In Access, I right-clicked on the linked Excel table, went into design view and it says that F1 is "Short Text", which is right. However, F2 is currency. While the currency format is right for all of the other cells in F2, the first cell is a date. So, instead of returning 12/1/2020, it returns $44,166. I can't change the format type because it's linked to Excel, but even if I could, it would change all the dollar amounts to dates, which I don't want. How do I break that cell out, so it's not in the same field (F2), or is there a better way to do this? Thank you in advance.

Answer 1

If you have only 1 row with a date rather than a currency value, it may be best to delete it from the worksheet.

Beyond that, you could attach the worksheet (External Data > New Data Source) and then run a query to select the non-first rows:

select * from theExcelTable

where F2 <> 44166;

You could then copy these rows to their final destination using an Append query.

Answer 2

Hi George,

Thank you for your reply. I understood that Excel stores dates as sequential serial numbers so they can be used in calculations. So, the dollar amount I was receiving was the sequential number for 12/1/2020. I just didn't know how to make it a date. That said, you mentioned "One thing I do think you could consider, though, is to use a query to format the values returned in your linked table conditionally depending on what you want to see in the various rows." Can you elaborate on that a little? I'm new to Access, so I don't really follow. Thank you in advance for your help. I'm also new to this community, so how do I give you credit for replying?

Answer 3

I believe you may be running into one of the snags that can plague linking Access to Excel. 

Actually, it is true that 44166 means the same thing as December 1, 2020, in the US date display format!

That's because dates are actually stored internally as decimal values, with the part of the number to the left indicating the number of elapsed days since the epoch starting date. I.E. 12/1/2020 (US format) is 44,166 days after 12/31/1899  (if I am doing the math correctly, anyway).

What you see as 12/1/2020 is the FORMATTED version of that value.

In importing or linking, that date value, Access is seeing it as the number. When linking to the Excel file, therefore, you need to address the problem in a more robust fashion.

You indicate that in the Excel sheet, column F2 contains a mix of currency in the rows following the first, but it is a date in the first row. What's happening is that the linking function is making it's best guess, based on the other rows and converting the FORMAT of the first row to match.

Off the top of my head I don't see a simple way to fix this because Excel allows for sloppy data typing that can't work in Access.

Hopefully, though, knowing why it is happening can help you come up with an alternative.

One thing I do think you could consider, though, is to use a query to format the values returned in your linked table conditionally depending on what you want to see in the various rows.