Skip to content
GridBelt
  • Excel Test
  • Practice
  • Programs
  • Pricing
Sign in
Start the free test
  • Excel Test
  • Practice
  • Programs
  • Pricing
Sign inCreate account
Start the free test
GridBelt

Practice Excel in your browser.

Excel Test

  • Level 1: Beginner
  • Level 2: Intermediate
  • Level 3: Expert
  • Excel test in 7 days
  • Leaderboard
  • Excel test for interview
  • Indeed Excel assessment

Practice

  • All exercises
  • Programs
  • For beginners

Guides

  • Excel formulas
  • Excel shortcuts

Company

  • About
  • Pricing
  • How we grade

© GridBelt. All rights reserved.

  • Privacy Policy
  • Terms of Use
  • Refund Policy

Excel is a trademark of Microsoft Corporation. Indeed, TestGorilla, SHL and other marks are the property of their owners. GridBelt is an independent practice site and is not affiliated with, endorsed by or sponsored by Microsoft, TestGorilla, SHL or Indeed.

  1. Practice
  2. /
  3. Date functions

Text to date practice: 200 orders from three systems, converted and counted per month

  • Expert
  • ~12 min
  • Date functions

Three systems feed the 2026 order log and each writes the date as text in its own shape: the US web shop as mm/dd/yyyy (99 orders), the German warehouse system as dd.mm.yyyy (57), the ERP as yyyymmdd (44). For example, ORD-70001 from the US web shop reads 01/04/2026 (mm/dd/yyyy) and means 2026-01-04; ORD-70002 from the DE warehouse reads 05.01.2026 (dd.mm.yyyy) and means 2026-01-05; ORD-70012 from the ERP reads 20260123 (yyyymmdd) and means 2026-01-23. Text dates cannot be sorted, filtered by month or subtracted, so convert all 200 of them into real dates in D2:D201, format the column as dates, and count the orders per month in H2:H13.

What you need to do

  1. 1Look at column C: the ERP strings are exactly 8 characters, the German ones contain dots, the US ones slashes. That is enough to tell the shapes apart with LEN and MID.
  2. 2Click D2 and build the date from its pieces with DATE(year, month, day): =IF(LEN(C2)=8,DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2)),IF(MID(C2,3,1)=".",DATE(RIGHT(C2,4),MID(C2,4,2),LEFT(C2,2)),DATE(RIGHT(C2,4),LEFT(C2,2),MID(C2,4,2)))). Press Enter — D2 shows 46026, the serial number of 2026-01-04.
  3. 3Fill D2 down to D201 (select D2:D201, Ctrl+D on Windows / ⌘D on Mac), then apply a date format to D2:D201 (Ctrl+1 / ⌘1 → Date, or Ctrl+Shift+# on Windows / Control+Shift+# on Mac). No cell may show #VALUE!.
  4. 4Click H2 and count the orders of the month in G2: =COUNTIFS($D$2:$D$201,">="&G2,$D$2:$D$201,"<="&EOMONTH(G2,0)) — from the first to the last day of that month. Fill down to H13; the twelve counts add up to 200.

Answer cells

Feedback

Type your formula in the grid, press Enter, then Check. We grade the value in each answer cell and the formula behind it.

Tasks

  1. Not checkedIn D2, convert the text in C2 into a real date — 8 characters means yyyymmdd, dots mean dd.mm.yyyy, slashes mean mm/dd/yyyy; build it with DATE(year, month, day) from LEFT/MID/RIGHT pieces inside IF — then fill down to D201.
  2. Not checkedFormat D2:D201 as dates (any date format) so the converted values display as calendar dates.
  3. Not checkedIn H2, count the orders whose converted date in D2:D201 falls in the month shown in G2, then fill down to H13.

Desktop recommended for spreadsheet tasks. The sheet scrolls inside this box.

StartInsertFormulasDataView
A1fxPreparing your workbook…
A
B
C
D
E
F
G
H
I
J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sheet1

Enter your formula in the sheet, then check.

About this exercise

A 200-row dataset on one sheet, 2 answer cells, about 12 minutes. Graded in your browser: the value and the formula behind it, with feedback that names the fix.

How to solve it

  • D2=IF(LEN(C2)=8,DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2)),IF(MID(C2,3,1)=".",DATE(RIGHT(C2,4),MID(C2,4,2),LEFT(C2,2)),DATE(RIGHT(C2,4),LEFT(C2,2),MID(C2,4,2))))
  • H2=COUNTIFS($D$2:$D$201,">="&G2,$D$2:$D$201,"<="&EOMONTH(G2,0))

A date that arrives as text is just characters to Excel, so the fix is to rebuild it: DATE(year, month, day) returns a genuine date serial from three numbers, and LEFT, MID and RIGHT supply those numbers from the right positions of the string. Because the three systems use three fixed shapes, two tests are enough to tell them apart — an 8-character string is yyyymmdd, a dot in position 3 means dd.mm.yyyy, otherwise it is mm/dd/yyyy — and IF routes each row to the matching DATE call. Once column D holds real dates, COUNTIFS can count a month as "on or after the first day and on or before the last day", which EOMONTH(G2,0) provides.

Pitfall 1: DATEVALUE depends on the regional settings of the machine — in a US locale it reads 03/14/2026 but returns #VALUE! for 14.03.2026 and 20260314 (and in a German locale the opposite happens). Rebuilding with DATE is locale-proof, which is why it is the method of record for mixed exports. Pitfall 2: DATE accepts the text pieces ("2026") and converts them itself, but a stray space or a non-breaking space in the source turns the piece into #VALUE! — wrap C2 in TRIM when the export is dirty. Pitfall 3: the converted column shows serial numbers until you format it; and if you later type a date over a formula cell, that cell stops updating — keep a text column and a converted column rather than overwriting. Variants: =--TEXT(C2,"0000-00-00") converts the 8-digit shape in one step; =DATEVALUE(MID(C2,4,2)&"/"&LEFT(C2,2)&"/"&RIGHT(C2,4)) rebuilds the German shape as a US string (locale-dependent again). Text to Columns with the "Date: DMY/MDY/YMD" setting converts one shape at a time without formulas, and Power Query does all three with a locale per column. Grouping: COUNTIFS with a date range is the pivot-free way to count per month; SUMPRODUCT((YEAR($D$2:$D$201)=YEAR(G2))*(MONTH($D$2:$D$201)=MONTH(G2))) gives the same result and extends to any grouping. A pivot table groups dates by month with one click — but only on real dates, never on text.

Other ways that work

  • =IF(B2="ERP",DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2)),IF(B2="DE warehouse",DATE(RIGHT(C2,4),MID(C2,4,2),LEFT(C2,2)),DATE(RIGHT(C2,4),LEFT(C2,2),MID(C2,4,2)))) — decide by the Source column instead of the string shape
  • =IFS(LEN(C2)=8,DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2)),ISNUMBER(FIND(".",C2)),DATE(RIGHT(C2,4),MID(C2,4,2),LEFT(C2,2)),TRUE,DATE(RIGHT(C2,4),LEFT(C2,2),MID(C2,4,2))) — IFS instead of nested IF (Excel 2019 and 365)
  • =SUMPRODUCT((YEAR($D$2:$D$201)=YEAR(G2))*(MONTH($D$2:$D$201)=MONTH(G2))) in H2 — month count without COUNTIFS criteria strings

Common mistakes

Relying on DATEVALUE: =DATEVALUE(C2) converts the 99 US-shaped rows and returns #VALUE! for the 101 German and ERP rows — the format a system writes must be parsed explicitly.

How we grade

Any formula that produces the right result is accepted, unless the task names a function: then another function earns half credit. We compare values with a small tolerance and ignore case and spaces in text. A correct number typed by hand instead of a formula earns half credit. Multi-cell tasks earn full credit at 100% of cells, half credit from 50%. Where it matters, we also change an input cell on a copy of your sheet and check that your formula follows.

Answers computed with Excel 365 rules and checked in our grading engine on November 16, 2026. Author: GridBelt team.

In these programs

  • Excel test in 7 days, Level 2 planDay 6
  • Excel test in 7 days, Level 3 planDay 6
  • Excel for Data AnalystsDay 11

Related exercises

  • Invoice due dates with EOMONTH and EDATEIntermediate, ~6 min
  • Days to deadline and tenure in years (TODAY, DATEDIF)Beginner, ~6 min
  • Extract category, year and serial from SKUBeginner, ~6 min

Function reference

Official documentation

Next exercise

List unique customers (UNIQUE) — remove duplicates