Software

Convert and format values into decimal values that Excel can interpret as time

Date arithmetic isn't for the timid, but this short lesson will help get your users headed in the right direction.

Dates and times in Excel are confusing to users, but here's something users can build on:

  • Excel stores date and time values as numeric values.
  • Integer values evaluate to days.
  • Decimal values evaluate to time.

Users will still stumble, but if they can relate a stored value to one of the above statements, they'll be a step closer to understanding the difference between what Excel stores and what it displays. The next obstacle is understanding how Excel turns 40827.5 into 10/11/11 12:00 PM. It's simple formatting. Excel stores the numeric value and displays a date/time string, depending on the format the user applies.

We can break down the above generalities into a simple exercise that should help users make sense of the relationship between stored and displayed values. Specifically, we'll convert a few time values into a more meaningful format.

For example, it's common to see time values that represent elapsed time - 1.5, 1.25, 3.75, 1, 2.3, and so on - in a format that Excel can't properly interpret. As is, these values represent hours and minutes where the integer value represents the hour unit and the decimal value represents the remaining minutes. For instance, 1.5 is one and a half hours, 1.25 is one and a quarter hour, 3.75 is three and a three-quarter hours, and so on. Excel can't format these values, as is, as just a time string because they include an integer value.

The following sheet is a good example of this problem. Columns A and B contain the same values, but the values in column B have been formatted using the Custom format h:mm. As you can see, Excel doesn't interpret the literal values in a useful way - it tries, but it just doesn't work as users might expect. Excel interprets the integer value as a day and the decimal value as a time, which it can't display in a meaningful way using the h:mm format.

The answer is a formula that converts the source number into a decimal that Excel can evaluate as a time value. In the example sheet, I've entered =A2/24 into C2 and copied the formula to cells C3:C5. As you can see, the formulas return decimal values that Excel can interpret correctly.

To format the resulting decimal values as time values, do the following:

  1. Select C2:C5.
  2. Right-click the selection and choose Format Cells.
  3. Choose Custom from the Category list.
  4. Enter h:mm in the Type control, and click OK.

Now, the format displays time values in a meaningful way: 1.50 becomes 1:30 or one hour and 30 minutes; .75 becomes 45 minutes; 2.25 becomes 2:15 or two hours and fifteen minutes, and so on.

This is just the beginning for your users - a way to help them distinguish between what Excel stores and what they see. Working with date and time values is the best way to learn though, so encourage your users to explore, not avoid, date and time values.

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
dhays
dhays

I use a spreadsheet to keep track of a set of items received for review, in this workbook I have sheets to show what is due per month--one for each month, a summary sheet of what was received by category and which group worked the item, a sheet used to calculate the number of items received per month along with the received date and due dates calculating time between, a sheet to keep track of all receipts per month and by due date which feeds the summary sheet graphs. The data is copied from an Outlook Task (start dates) [copy and paste--to directly export from Outlook copies too many unneeded and unused data points]. Which in turn would still require filtering, it is much easier to copy and paste. One of my co-workers set up a workaround for me to enable a correct count of items received per month. He setup the following formulat to convert each date to text: TEXT(INDIRECT("'Processing Time'!C"&$D25+1),"mm/dd/yyyy") where processing time is the name of the sheet where the information is found. This enables a countif function to correctly count the number of items with a certain month. Without this extra step the count is incorrect (for the month of September, for example the countif alone counts 28, however forcing each to be text the count is 35) This way I don't have to go into each entry and ensure it is formatted as text. Sometimes when copying the whole date (e.g. Tue Nov 1, 2011) it would copy as text with an appostrophe and sometimes I would have to add one when I deleted the day of the week leaving the numerical date) With the workaround I don't have to worry about what format it is in, the subtraction works either format or mixed formats, it is just the countif function that has problems.

Editor's Picks