What Are the Units of Excel Date/Time Values?

[SidBord]

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?

 

[PaddyD]

"No, it’s not minutes. Reread the first sentence above about how Excel stores a date time value. "

Read the excel help topic "How Microsoft Excel stores dates and times"

Days are integers; times are decimal fraction.

To see this, enter a date and time in a cell, then format as a number. The number is the stored value.

 

[Fat Cat]

1 day = 1.0
1 hour = 0.04166666667 days
1 minute = 0.00069444444 days
1 second = 0.00001157407 days

give or take the odd decimal place

 

[SidBord]

I completely understand the way Excel has implemented date/time values. That wasn't my question. My question deals with the "units" associated with a date/time value. It would appear that you agree with my assumption that the unit is "day", even if the value is formatted as a time like 1:30:00. One can guess about the units of most factors in a formula (60 min/hr, 24 hr/day, etc.), but when I encountered 1:30:00, I was lost. Thanks for the response.

 

[PaddyD]

"I completely understand the way Excel has implemented date/time values"

Doesn't that preclude you asking any questions about it

"It would appear that you agree with my assumption that the unit is "day" "

There is no need to assume this - it's exactly as set out in the help entry I directed you to:

"Microsoft Excel stores dates as sequential numbers known as serial values. Excel stores times as decimal fractions because time is considered a portion of a day. Dates and times are values and, therefore, can be added, subtracted, and included in other calculations. For example, to determine the difference between two dates, you can subtract one date from the other. You can view a date as a serial value and a time as a decimal fraction by changing the format of the cell that contains the date or time to General format"

Anywya - glad you've got it sorted.