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.

1 comments
patel gaurav
patel gaurav

I want to create a excel sheet in which there will be some column which will get the input in alphabets and numeric values. The last column will be for total. Irrespective of any alphabets and numeric values in the backend the excel should take 10 by default and if the cell is left blank then it should take value as 0. So in the total it should only add 10 for every cell, which have some alphabets and numeric values. Que 1 Que2 Que3 Total abc 5 20(Front End) 10 10 20(Back End)

Editor's Picks