It was a Thursday, and the project tracker looked perfect. Every task had its duration written next to it: 5 days, 3 days, 8.5 days. The column was tidy, the figures were right, and the total at the bottom said zero.
Nothing was wrong with the numbers. That was the maddening part. The cells held exactly what I had typed — and that was the problem. The moment I typed "5 days" into a cell, Excel stopped seeing a number. It saw a word. SUM, AVERAGE and every formula that touched the column quietly skipped those cells, and the total came back as zero without a single error to explain itself. No warning, no red triangle, just a confident-looking wrong answer.
The usual rescue is a helper column: numbers in one column, units typed in the next. It works, and it doubles your columns, clutters the sheet, and still leaves the raw numbers looking bare. There is a neater way, and it has been sitting inside Excel the whole time.
A custom number format changes only how a number looks, never what it is. The cell still holds 5; it simply displays "5 days". Formulas keep working, sorting keeps working, and the sheet reads the way you want it to. The code itself is short — something like 0.0 "days" gives you one decimal place with the word days after it — but writing it from memory means looking up the syntax for quotes, decimals, thousands separators and spacing every single time. I build mine now with this free Excel custom number format generator, which writes the code while I fill in the boxes: https://toolbrewlab.com/excel-custom-number-format-generator/
There is one wrinkle worth knowing before you paste the code anywhere. "1 days" reads wrong, and a good format handles it: a condition inside the code makes 1 display as "1 day" while 5 still shows "5 days". It is a small touch, and it is the difference between a sheet that looks hand-finished and one that looks generated. The same idea covers rates — put "$" before the number and "per hour" after it, and 45.5 displays as "$45.50 per hour" while staying fully calculable for invoice totals.
One caution if you work across both apps: the basic building blocks work the same in Google Sheets, reached through Format, Number, Custom number format. The singular-word condition is the one place to test first, because conditions behave slightly differently there, and it is meant for values that are not negative. For plain units with no singular form, the same code works in both.
The habit that stuck with me is simple, and it has saved more totals than any formula I know: numbers stay numbers, and formatting does the talking. Once a sheet is set up that way, adding a unit is a one-click job instead of a data-cleaning exercise — and the total at the bottom finally says what it means.
Top comments (0)