What Are the Units of Excel Date/Time Values?

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?
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
"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

Active Member
Joined
Nov 5, 2004
Messages
336
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

Active Member
Joined
Aug 23, 2004
Messages
346
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. (y)
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
"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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,016
Messages
5,569,628
Members
412,284
Latest member
Daibear
Top