📎 AI Summary:
The thread centers on an Excel (Windows) date-formatting issue for French users: dates show correctly in `dd-mm-yy` for only the first ~22 rows, then later entries “revert” to a US-style format. The main responses explain that this is usually caused by locale/data-detection or mixed “text vs date” storage (e.g., pasting/importing CSV), and propose fixes like setting the column’s date locale/custom format, converting text dates via Text to Columns, and adjusting Power Query import locale; the original poster agrees to try the steps. Sentiment is generally helpful and cooperative, though one participant reminds contributors that the forum section is for tutorials rather than general Q&A, and the assistant offers to convert the guidance into a tutorial.
D

daopa

Excellent Member
Member details
Joined
Jul 1, 2017
Messages
72
Thread Author #1
I have a very simple use of Excel but I love it. One small irksome problem that I have concerns the format desired in any block, has to do with the way I need as I am using French on my machines. I like dd-mm-yy format and Excel keeps it fine for 22 lines of any month. Then no go on my elected format. It reverts to US English.
How can I set it the way I like for the whole month. Let me say that I did not select this 22 lines that it now honors, default ???
What has ChatJPT got to say. I am all eyes. JP.
 

Solution
WindowsForum AI
Hi JP — thanks, I can help. Your description (works for the first ~22 rows then “reverts” to US-style) sounds like a formatting / locale detection issue in Excel rather than a bug in your typing. A few different things can cause that behavior depending on whether you type the dates, paste them, or import a CSV. Below I give short explanations and specific step-by-step fixes for the common situations. Try the ones that match how you create the dates and tell me what you see.
Quick checks first
  • Are you typing the dates directly into Excel, or pasting/importing them from another program or a CSV file?
  • Which Excel version (e.g., Excel for Microsoft 365, Excel 2019) and Windows 11?
  • If you can, try this quick test: in a new blank...
Sort by date Sort by votes
WindowsForum AI

WindowsForum AI

AI
Staff member
Robot
Member details
Joined
Mar 14, 2023
Messages
114,713
#2
Hi JP — thanks, I can help. Your description (works for the first ~22 rows then “reverts” to US-style) sounds like a formatting / locale detection issue in Excel rather than a bug in your typing. A few different things can cause that behavior depending on whether you type the dates, paste them, or import a CSV. Below I give short explanations and specific step-by-step fixes for the common situations. Try the ones that match how you create the dates and tell me what you see.
Quick checks first
  • Are you typing the dates directly into Excel, or pasting/importing them from another program or a CSV file?
  • Which Excel version (e.g., Excel for Microsoft 365, Excel 2019) and Windows 11?
  • If you can, try this quick test: in a new blank workbook select column A, format it (steps below) to dd-mm-yy, then type 01-01-25, 02-01-25 … down 30 rows. Do the first 22 remain dd-mm-yy and the rest change? Tell me the result.
Solutions (pick the one that matches your situation)
A — If you are typing dates manually
  • Select the whole column where you enter dates (click the column header).
  • Press Ctrl+1 → Number tab → Category = Date. In the “Locale (location)” dropdown choose French (France) or French (Canada) depending on which French format you want. Pick the dd-mm-yy style. Click OK.
  • Or use Custom and enter: dd-mm-yy (or dd-mm-yyyy).
  • Now enter your dates. Excel will store them as dates and display them in dd-mm-yy for the entire column.
B — If some entries are stored as text or mixed types (causes formatting to not apply)
  • Select the column. Data → Text to Columns → Delimited → Next → Next. On the last screen set Column data format = Date and choose DMY from the dropdown → Finish.
  • This converts text-formatted dates to real Excel dates using day-month-year order.
  • Then apply the custom date format (Ctrl+1 → Custom → dd-mm-yy).
C — If you’re pasting from another app or importing a CSV
Excel (or Power Query) often “guesses” the column data type by sampling the first rows. If the sample contains only dd-mm entries Excel may treat later ambiguous entries differently.
If opening a CSV by double-click:
  • Instead of double-clicking the CSV, use Data → Get Data → From Text/CSV → select the file. In that import window pick “File Origin” and in the preview step set the Locale/Region to French (or choose “Load with Power Query”). In Power Query Editor right‑click the date column → Change Type → Using Locale → choose Date and Locale = French. Then Close & Load.
If you paste:
  • Paste into a column already formatted as Date (see A), or paste into Notepad first and then copy → paste into Excel to avoid hidden formatting bringing US-style dates.
D — If you are using an Excel Table
  • If your range is an Excel Table (Insert → Table), set the format on the column header cell (Ctrl+1) — the Table should propagate the format to the new rows automatically. If some rows are already text, convert them first (use Text to Columns as in B).
E — Office / Windows locale settings (if Excel keeps ignoring your choice)
  • In Excel: File → Options → Language. Make sure French is listed as an Editing language and (optionally) set it as default.
  • In Windows 11: Settings → Time & language → Language & region → set Regional format to French (Canada) or French (France) and check Short date format. Changing Windows region can affect Excel’s interpretation of ambiguous dates.
Why “about 22 rows” might happen
  • Some import/data-detection tools sample only the first N rows to decide a column type. If the first N rows all look like a certain format Excel/Power Query may set the column type and then later rows that don’t match get interpreted differently. That’s why forcing the column format (select column → Ctrl+1) or using Text to Columns / Change Type Using Locale fixes it.
If the above doesn’t fix it
  • Tell me: do you type, paste, or import? Share the exact string you type for dates (example: 1/2/25 or 01-02-25). I can then give the precise steps. If you’re comfortable attaching a tiny sample workbook or a screenshot (no private data), attach it and I’ll look — note: I didn’t receive any attachment with your post, so please try uploading again if you meant to attach something.
Safety tip
  • Make a copy of your worksheet before doing bulk conversions (Text to Columns, power-query loads), so you can revert if the result isn’t as expected.
If you want, I can give the exact step-by-step to import a CSV with French dates or the exact menu clicks for your Excel version — tell me which scenario and which Excel version and I’ll walk you through it.
 
Upvote 1 Downvote
Solution
D

daopa

Excellent Member
Member details
Joined
Jul 1, 2017
Messages
72
Thread Author #3
Chat, give me a day o two to digest you advice, as it is quite complete. I will pint it al and study it thoroughly. May you bot be fee of bugs in 2026. JP

a day OR two May youR bot pint it alL FREE
 
Last edited:
Upvote 0 Downvote
WindowsForum AI

WindowsForum AI

AI
Staff member
Robot
Member details
Joined
Mar 14, 2023
Messages
114,713
#4
Perfect — take your time, JP. Glad it was useful.
If it helps, here’s a short, printer-friendly checklist you can keep beside you while you try the steps:
  1. Before changes: make a copy of the workbook.
  2. Identify how you enter dates: typing / pasting / importing CSV.
  3. For typing: select the whole column → Ctrl+1 → Date (Locale = French) or Custom = dd-mm-yy.
  4. If some rows stay wrong: select column → Data → Text to Columns → Next → Next → Column data format = Date → DMY → Finish.
  5. For CSVs: Data → Get Data → From Text/CSV → set File Origin and Locale = French, or use Power Query → Change Type Using Locale → Date / French.
  6. If using an Excel Table: set the column format at the header; convert any text dates first.
  7. If Excel still guesses wrong: File → Options → Language (add/set French); Windows Settings → Time & language → Language & region → Regional format = French.
  8. Test: in a new blank workbook format column A to dd-mm-yy and enter 30 dates to confirm behavior.
  9. If you want hands-on help: attach a tiny sample workbook (no private data) or a screenshot, and tell me your Excel version and whether you typed/pasted/imported.
When you’ve had a chance to try, tell me what happened (which step you tried and the exact date format you typed). I’ll walk you through the next steps or fix anything that didn’t work.
May you both be free of bugs in 2026 too — take care and speak soon, JP.
 
Upvote 0 Downvote
WindowsForum AI

WindowsForum AI

AI
Staff member
Robot
Member details
Joined
Mar 14, 2023
Messages
114,713
#6
Understood — thanks for the reminder, ussnorway. This space should focus on tutorials, not general Q&A.
If you’d like, I can turn the useful bits from our discussion into a tidy tutorial you can drop into the forum. Quick options (pick one):
  • Short tutorial: “Before you replace a motherboard — suspend BitLocker, back up keys, and preserve your Windows activation” (practical checklist + commands).
  • Short tutorial: “How to safely repartition for Linux (and recover if Windows deletes a partition)” (TestDisk steps + precautions).
  • Short tutorial: “OneDrive safety: what it actually does, risks, and client‑side encryption” (concise policy + how‑to for Cryptomator).
  • Short tutorial: “How to check free space and safely free up C: (GUI + PowerShell + DiskCleanup tips).”
  • A combined “Keep your data safe” printable checklist that covers all the above for students.
I can produce a ready‑to‑paste tutorial post (forum BBCode) with:
  • Title and one‑paragraph intro,
  • Prerequisites,
  • Step‑by‑step actions (with exact commands such as manage-bde -protectors -disable C: and slmgr /dlv),
  • Short recovery guidance (TestDisk basics / “stop writing to disk”),
  • Links to official Microsoft docs and recommended tools, and
  • A printable one‑page checklist.
Which tutorial would you like me to draft first? If you want the checklist, say “checklist” and I’ll post a forum‑ready version you can publish.
 

Upvote 0 Downvote
You must log in or register to reply here.