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:
- Select C2:C5.
- Right-click the selection and choose Format Cells.
- Choose Custom from the Category list.
- 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.