Skip to content
Reading settings
ExcelBeginner~20 min practiceLesson 4 of 34

Recognize numbers, text and dates

Keep identifier zeros, convert text amounts and build dates with DATE.

Content reviewed · How we check lessons

Loading your progress…

In this lesson

Separate codes from quantities and convert an import without losing zeros or guessing a date format.

Example

A staff import with mixed data types

The import contains staff code 00127, amount 1200 stored as text and date 03/04/2026. The code needs its leading zeros. Before converting the date, the source owner must confirm whether it means March 4 or April 3.

How it works

Digits do not always represent a quantity. Staff codes and postal codes often belong in text fields so leading zeros survive.

Excel stores dates as serial numbers and formats them for display. Formatting can change how a date looks, but cannot repair a day and month that were interpreted the wrong way round.

Steps

  1. Enter StaffCode, AmountText, AgreedDate and AmountNumeric in A1:D1. Format A2 as Text, then type 00127. In B2, type an apostrophe followed by 1200.

  2. Enter the formula below in D2. Label E1 AmountIsNumeric and enter =ISNUMBER(D2) in E2: it should show TRUE. Check =ISNUMBER(B2) separately; that should show FALSE.

    =VALUE(B2)

    English function names. Formula settings →

  3. For this exercise, use April 3, 2026. Enter =DATE(2026,4,3) in C2 and format it as yyyy-mm-dd on desktop, or choose a clear date display in web Excel.

  4. Enter =C2+7 in C3 and format it as a date. The result should be April 10, since adding 7 to a date adds seven days.

  5. Enter 950 and 700 as text in B3:B4. Fill the conversion formula from D2 down to D4, then enter =SUM(D2:D4) outside the range. The total should be 2850.

  6. Before copying dates from an older workbook, check its date system. Windows and Mac files can use different systems. Record the setting before changing it so you can investigate a shifted date.

Try it yourself

  1. Create a new code 00008 as text and a numeric amount 45.5 without converting the identifier. Explain which field should pass ISNUMBER and which should not.
  2. Build May 6, 2026 with DATE rather than typing 06/05/2026. Add ten days and write both dates in a handover note using year-month-day order.
Show expected result
  • StaffCode remains 00127, and the three numeric amounts total 2850. The code is intentionally text while converted amounts are numbers.
  • The exercise date is May 6 and ten days later is May 16, 2026; April 3 plus seven days is April 10.

If something goes wrong

00127 becomes 127.
Format a fresh destination as Text before entering or importing the code. A custom zero format changes the display only; it cannot restore digits already lost from an identifier.
VALUE fails on an imported currency amount.
Check the source currency symbol, decimal separator and thousands separator. Use the matching import locale or conversion settings; removing punctuation indiscriminately can change the amount.

Quick check

Try these questions after the exercise. Check the result in your app before marking the lesson complete.

1. Which entry should normally retain its leading zeros as text?
2. What does =DATE(2026,4,3)+7 represent?
3. Does applying currency formatting guarantee that a text amount becomes numeric?

Choose an answer for all 3 questions to check your understanding.

Optional: try it with Copilot

If you have Copilot, try this after the exercise. Keep the original file and compare its suggestions with your own result.

Sort these fields into codes, quantities and dates. Tell me which dates need clarification and which locale settings matter before converting the amounts.

More from Microsoft

These Microsoft guides cover the tools used in this lesson.

Loading your progress…