Software

Office solution: Why Excel sometimes displays dates instead of values

This week, learn the possible solutions to last week's challenge: Why is Excel displaying a date instead of a numeric value?

Last week, your challenge was to help a user who entered a numeric value, but Excel displayed a date. I thought I'd really stump you this time, but Hometoy was the first to suggest the most common reason this happens—the user inserted a column. When inserting a column, Excel uses the formats from the column to the left. The figure is the clue; there's a date column to the left.

Users can't avoid this formatting behavior when inserting a column. Knowing what to expect can prevent anxiety and calls to you, but the solution is to apply the appropriate formats after inserting the column.

Shriks and Rudi-S discussed a similar situation when using formulas. If a formula refers to a date, the result will be formatted as a date. The challenge's example doesn't use a formula, but Shriks and Rudi-S are right.

In addition, Ppg mentioned a possibility that I hadn't considered: if the user copied or entered a date first, Excel assumes a date format, even if the user deletes the date value and enters a numeric value. Nice catch Ppg!

Thanks to all of you for enhancing the conversation with these possibilities.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks