Ravindra BagaleCourses & study guides Track your progress
Medium challenge

Medium: Date serial shown as number

By · Free ·

Absolute refsNumbers as textLeading spacesFilter left onChart range% vs decimalDuplicate headerROUND vs displayFreeze panesPaste valuesVLOOKUP & textVLOOKUP col indexHLOOKUP TRUESUMIF spacesCOUNTIFS ORIF nest orderDate serialINDEX MATCHWildcard COUNTIFMixed currencyCircular & datesCircular interest3D sum renameNamed range staleCF vs IFPivot staleHard-coded NPVValidation movedPQ step orderTyped-over formulaSUM wrong anchorsFiscal year Apr–MarFind all bugsInterview oral

Date column is General — you see serials like 45980 instead of 2026-09-15. Apply YYYY-MM-DD format or =TEXT(C5,"YYYY-MM-DD") for labels. MONTH() still works on the serial.

About this data: Made-up invoices for Farah Qureshi (Lucknow) and Ben Adams (Toronto). Practice only.
Download broken workbook Download fixed (after you try)

Why → What → How

Why: Imports and copy-paste strip date formats. Managers panic at “huge numbers”.

What: Four invoices; broken shows serials; fixed formats + TEXT labels.

How:

  1. Open Invoices.
  2. Select Date column → format as date.
  3. Optional Label with TEXT.
  4. Confirm MONTH(C5)=9.

Ravindra Bagale’s Tip

Serials are normal under the hood. Fix the number format before rewriting formulas.

Bugs explained (with mini examples)

Bug 1 — General format on dates

Looks like 5-digit numbers.

Bug 2 — Rewriting dates as text by hand

Breaks MONTH/YEAR — format instead.

Practice tasks (12)

  1. Task 1. What does broken C5 look like?
    Hint: Serial number.
  2. Task 2. Apply date format.
    Hint: YYYY-MM-DD.
  3. Task 3. TEXT label formula.
    Hint: =TEXT(C5,"YYYY-MM-DD")
  4. Task 4. MONTH(C5)?
    Hint: 9.
  5. Task 5. Interview sentence.
    Hint: “Dates were serials — format issue.”
  6. Task 6. Does deleting and retyping help?
    Hint: Risky.
  7. Task 7. YEAR of last invoice.
    Hint: 2026.
  8. Task 8. Keep real dates, not text dates.
    Hint: Yes.
  9. Task 9. Why TEXTJOIN avoided here.
    Hint: LO verify simplicity.
  10. Task 10. Bonus: custom format DD-MMM-YYYY.
    Hint: Display.
  11. Task 11. Sort by Date after fix.
    Hint: Chronological.
  12. Task 12. Note for juniors.
    Hint: A11.

In-browser formula check

Type the fixed formula you would put in the named cell. We check formula text — open the workbook for full recalculation.

Step-by-step solution (try first)
  1. Format C as date; Label =TEXT(…).
  2. First label 2026-09-15.

Quick quiz

Q1. A 5-digit value in a Date column often is:
Q2. MONTH works on serial dates:
Q3. Preferred fix:
Q4. Expected first label:
Q5. Rewriting dates as plain text can break: