US to Canada Conversion issues

Ok. It is definitely a date problem. I took a sample file with some dates and amounts and used File/Advance/Language&Region to change the region to Canada. The screenshot below is the result. The top table is after I changed to Canada. The bottom is the original table. The numbers at the top should be the monthly totals but the Canada version is not correct. Now to figure out why...

I believe this is a bug. I used the YEAR, MONTH and DAY functions to break all those dates into their component pieces and the dates all stay the same when changed to/from Canada but the comparison in the SUMIFS breaks nonetheless. I can make it work fine if I change the date format to 2020-01-05 format for the two dates in the SUMIFS. I can leave the column of dates unchanged but you might want to be consistent and change all your dates to that format.

The Canada region date settings does not have the short date format "1/5/21". All the numeric dates for Canada are year/month/day. So it is probably something to do with that.

Here it is in Canadian region with the date format changed.

Watch out for the Canadian dates in English/French.

Showing the template "Academic Calendar" in English/Canada and in French/Canada.

The "standard/default" date format in Canada looks like it is dd/mm/yy (instead of the mm/dd/yy date format in the USA).

Paul.

"In the image below you can see that January and February entries have been added together and then adds it to every month thereafter with no information yet entered for those months."

Do we see "the January and February entries" in the provided image, or do we see the results of a formula that 'sees' those entries?

Assuming a formula,

What is the formula in the cell directly under "January"?

What is the formula in the cell directly under "February"?

What is the formula in the cell directly under "March"?

Are the formulas in the bottom three rows of the image behaving as expected?

Regards,

Barry

In addition to the formulas, we need to see the table(s) the formulas refer to. If they are SUMIF formulas and they are not summing what you think they should, we have to be able to see the formula to see the ranges for the test values and sum values and the condition that needs to be met. Then we need to see the data table that has the test values and sum values.

It sounds like you want to sum up entries by month. In the first row, are the "months" text or are they date&times formatted to show only the month? Same question might apply to the data table if it is not clear from the screenshot you will post of that table.

When they "convert to Canadian currency", are they just changing the currency symbol or are they using an exchange rate and actually changing the numbers, too? I do not understand why they would do either, actually. Changing the symbol is wrong (unless these numbers actually are CA$) and they would probably not know the exchange rate for each transaction to do an accurate conversion. Why are they changing anything?

One more time on the workflow. This what I heard, maybe:

You work on it in Numbers. All is good

You export it to Excel. No complaints from them that anything is wrong

They work on it in Excel. I assume it still looks good

You reimport it into Numbers. The problem begins

I suspect it has something to do with the dates. The dates in cells C3 & C4 and in column A of the 'Commission - View Only' table, are they actual date&time values or are they text? Do the other people working on this spreadsheet use MM/DD/YY format or DD/MM/YY format for their dates? I'm trying to figure out what combination of text vs dates or MM/DD/YY vs DD/MM/YY vs YY/MM/DD or whatever might cause this but haven't come up with one.

From Wikipedia:

………

ISO 8601 is the only format that the Government of Canada and Standards Council of Canada officially recommend for all-numeric dates.[28][29][30] But, their usage differs depending of many contexts.[31][32]All three formats are used in Canada for long format.

For English speakers, MDY was preferred form (mmm-dd-yyyy) (Example: April 9, 2019) and used by nearly all English language publications and media companies as well as majority of English language government documents.[citation needed]

For both French and sometimes English speakers, DMY are used (dd-mmm-yyyy) (Example: 9 April 2019/le 9 avril 2019) and also used in formal letters, academic papers, military, many media companies and even some governmental documents, particularly in French-language ones.

Federal regulations for shelf life dates on perishable goods mandate a year/month/day format, but allow the month to be written in full, in both official languages, or with a set of standardized two-letter bilingual codes such as 2019 JA 07 or 19 JA 07.

………

Paul.