SidBord
Active Member
- Joined
- Aug 23, 2004
- Messages
- 346
Excel stores its type Date (date/time) values as a serial number containing an integer part representing a date and a fraction part representing a time value. My question is, what are the units for an Excel date/time value?
I’m interested because I often use what’s called dimensional analysis to help me assemble a formula in Excel. Dimensional analysis requires you to substitute the units of each item in an equation or formula for the corresponding numeric value. This technique can help you decide whether to multiply or divide two items when it isn’t apparent which you must do. It also helps determine if you need to add a constant like 60 minutes/hour to make things work out.
For example, you know the distance (in miles) that your car traveled on a trip, and you know how long it took to complete the trip (in hours). To determine the average speed the car traveled, you must divide one of those numbers by the other … but which one goes on the top?
Well you know that the end result has the units of speed (miles per hour or miles/hour). That’s a strong clue that when you divide, the distance value should go on the top of the division and the time should go on the bottom. So the formula would be = miles / hour. It’s not always so obvious when you are trying to figure out a complex formula.
For this technique to work, you MUST know the units for ALL factors, In the problem above, the unit of distance was specified as miles and the unit of time traveled was specified as hours. Suppose that instead of recording time traveled as, say, 30 minutes, you typed into your Excel spreadsheet 0:30:00, which is the Excel date/time format for 30 minutes. In this case what would the units of that stored value be? No, it’s not minutes. Reread the first sentence above about how Excel stores a date time value.
I have just about concluded that the unit of an Excel date/time value is “day”, but I’m unsure about that. Does anyone know for sure?
I’m interested because I often use what’s called dimensional analysis to help me assemble a formula in Excel. Dimensional analysis requires you to substitute the units of each item in an equation or formula for the corresponding numeric value. This technique can help you decide whether to multiply or divide two items when it isn’t apparent which you must do. It also helps determine if you need to add a constant like 60 minutes/hour to make things work out.
For example, you know the distance (in miles) that your car traveled on a trip, and you know how long it took to complete the trip (in hours). To determine the average speed the car traveled, you must divide one of those numbers by the other … but which one goes on the top?
Well you know that the end result has the units of speed (miles per hour or miles/hour). That’s a strong clue that when you divide, the distance value should go on the top of the division and the time should go on the bottom. So the formula would be = miles / hour. It’s not always so obvious when you are trying to figure out a complex formula.
For this technique to work, you MUST know the units for ALL factors, In the problem above, the unit of distance was specified as miles and the unit of time traveled was specified as hours. Suppose that instead of recording time traveled as, say, 30 minutes, you typed into your Excel spreadsheet 0:30:00, which is the Excel date/time format for 30 minutes. In this case what would the units of that stored value be? No, it’s not minutes. Reread the first sentence above about how Excel stores a date time value.
I have just about concluded that the unit of an Excel date/time value is “day”, but I’m unsure about that. Does anyone know for sure?