Date/Time values are POINTS IN TIME. There is a particular point in time, that is defined by January 1 2020 or December 25 2020 12:00 PM. The UNITS of Date/Time in Excel are DAYS.
Excel’s Date numbering system begins with Date Serial 1; Jan 1, 1990. Any Date that you might enter that is earlier than 1/1/1990 will be displayed as TEXT (left justified) that cannot be used in calculations.
DURATION is a measure of time between two points. DURATION may be expressed in terms of years, months, days, hours, minutes or seconds, or any combination thereof, but they are not Date/Time values. If you express a DURATION in Units other than Days, Number Formatting cannot be used. All considerations here are for DURATION in Days and Formatted in any number of different ways.
Context is king.
Let’s take this value:
31.25
What is 31.25? Well it’s just a number until you determine what that number represents. Putting aside the endless possibilities, our focus is Date and Time. So in this context, it could be a Date/Time Point in time or it could be a DURATION.
Here’s an example of 31.75 with several different Number Formats.
Notice that I have 4 Formatted Values that ALL have an underlying value of 31.75. The Number Formats are displayed beside each Formatted Value as per the lookup table.
If 31.75 is to be a Date/Time value, then it could be formatted as
Jan 31, 1990 6:00 PM. Format mmm dd, yyyy h:mm AM/PM
If 31.75 is to be a DURATION, then it could be Formatted as
762:00, 762 hrs 0 min (31.75 Days * 24 Hrs/Day) Format hh:mm or
31:18:00, 31 days, 18 hrs, 0 min, Format d:hh:mm.
Notice that there can be ambiguity in interpreting the data in the various ways that data can be presented. It is up to you, the designer, to clarify the data that you are displaying.
So far, we are using data in Units of Days. In this case, you might make a Note that All Units Expressed in DAYS, especially if people are going to be using your data.
Formatting does not change ANYTHING!!!
The underlying value remains unchanged.
Be sure that you clarify your data for the sake of the consumer.
Also, check out Microsoft on this topic.
I welcome your comments and questions if you are so inclined.
Check out Excel Changes Your Data. Be Aware!