What Are the Units of Excel Date/Time Values?

SidBord

Active Member
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?

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

MrExcel MVP
"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
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
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.

MrExcel MVP
"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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,114
Messages
5,835,462
Members
430,358
Latest member
zzc1128

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back