How do I calculate the difference between two times?

I don't know if it is the more basic operation but it is one of the more often asked and responded.

-+-+-+-+-+-+-+-

Entering the Help and Terms of Use area you will read:

*What is Apple Discussions and how can it help me?*

Apple Discussions is a user-to-user support forum where experts and other Apple product users get together to discuss Apple products. … You can participate in discussions about various products and topics, find solutions to help you resolve issues, ask questions, get tips and advice, and more.
_If you have a technical question about an Apple product, be sure to check out Apple's support resources first by consulting the application Help menu on your computer and visiting our Support site to view articles and more on our product support pages._

*I have a question or issue*—
how do I search for answers? _
It's possible that your question or issue has already been answered by other members so do a search before posting a question._ On most Apple Discussions pages, you'll find a Search Discussions box in the upper right corner. Enter a search term (or terms) in the field and press Return. Your results will appear as a list of links to posts below the Search Discussions Content pane.

-+-+-+-+-+-+-+-

in D2 enter:

=TIME(0,0,ROUND((TIMEVALUE(C)-TIMEVALUE(B)) 24*6060,0))

Yvan KOENIG (from FRANCE dimanche 27 juillet 2008 16:47:29)

When we do a search, the most important things to know are:

• enter directly with the "more options" button
• restrict the search to iWork '08 (or even to Numbers)
• use the operator AND to get a more precise search.

calculation AND time was an efficient keyString for your question.

Yvan KOENIG (from FRANCE dimanche 27 juillet 2008 18:31:23)

KOENIG Yvan wrote:
I don't know if it is the more basic operation but it is one of the more often asked and responded.

I believe this strongly implies that the Numbers design & user interface, & to a lesser degree its documentation, is flawed.

The fundamental design flaw is that Numbers has no "time" data type, just a "date-time" one. This is compounded by the interaction of this data type (& only this data type) with the cell formatting options: unlike with any of the pure number or the text formats, changing the format of a cell containing a date-like or time-like value may irreversibly change the value from what is manually entered into the cell to something else. (Example: enter "8:00 AM" into a cell preset to the text format, then change the format to "Date & Time" & Numbers will change the value to a fully specified date-time. Changing the format back to text will not restore the originally entered text string.)

The documentation is not entirely clear about this: while it does mention that in this case changing the format may change the value, it also mentions that the TIMEVALUE() function "converts a date, a time, or a text string to a decimal fraction of a 24-hour day." This implies to many that there are three possible data types (date, time, & text string) rather than two (date-time & text string).

The UI isn't very helpful in making this obvious either: the Cell Inspector offers a "Date & Time" format, & for each a "None" option, again implying to some users that these are two separate data types that happen to have there formatting options combined in one Inspector choice.

Likewise, the documentation (& the function name) tell us that "The TIME function converts hours, minutes, and seconds into a time format," once again implying to some that this is a distinct data type, & again the UI does not make this misconception entirely obvious: both "8:00 AM" & "=TIME(8,0,0)" look the same as a displayed value & each may be formatted to include or hide the date part of the data type.

While we probably can't expect Apple to add a time data type to this version of Numbers; in light of all the confusion about time, date, & date-time values, it perhaps would help to change the UI & documentation text to more explicitly make it obvious that there is just one data type, for example by changing the Cell Inspector format option from "Date & Time" to "Date-Time."

Does anyone else think this would be a good suggestion for Numbers feedback and/or ease the confusion? Since this just involves changing some text strings but no programing changes, it seems like something Apple could implement in a minor update....

Tired of your rants upon this subject.

There is no flaw in the help. There is only one user named RC-R which refuse to understand what is written.

Numbers stored date_time values, no less no more.

When we type only the time portion, it includes the current date.
When we apply the formula
=TIME(0,0,ROUND(TIMEVALUE(B) 24*6060,0))
we get a date_time value whose numerical value of the isolated date is 0 which means, in AppleLand 1 janvier 1904.
Such a date_time allow us to make correct calculations.
There are only two true drawbacks:

• as TIMEVALUE() returns a decimal number, when we calculate the corresponding number of seconds we MUST use the ROUND() function because as everybody knows, calculations with decimal numbers give the wanted value +- epsilon.

• as Numbers know only date_time values, the time part is always in the range 0:00:00 thru 23:59:59

There is also a detail which seems annoying for one user, the minimal time value is the second because time values are in fact a pseudo_string representing the _number of seconds_ between the date_time and the date_time 1 janvier 1904 00:00:00.

All other comments are quite rants.

Just a sample:

The documentation is not entirely clear about this: while it does mention that in this case changing the format may change the value, it also mentions that the TIMEVALUE() function "converts a date, a time, or a text string to a decimal fraction of a 24-hour day." This implies to many that there are three possible data types (date, time, & text string) rather than two (date-time & text string).

In fact the Help means only that the operand for the function TIMEVALUE() may be something like
31/12/1943, 31 décembre 1943, 31 déc. 1943 described as date
1:12:36, 1:12 described as time
31/12/1943 23:59:59 described as a text string
It may also be 31/12/43 but here the program must guess the century. The rule is given. This one will be 31/12/2043 (yes, I am very young).

All of this is not exactly what we are accustomed to but it is perfectly logical and described.
My guess is that those which doesn't understand are simply heavily linked to old habits putting a curtain between the description and their brain.

Yvan KOENIG (from FRANCE lundi 28 juillet 2008 14:28:17)

Okay,
I tried your formula, but for some reason my file is giving a red flag "the time returned by the formula isn't valid" for a few of the cells. Column B and C are formatted in the Date and Time Dialogue with "none" under Date and "2:10 PM" under Time. Column D is formated as a Number with 2 Decimals.
Below are a couple of pictures of the spreadsheet, one with the formula working successfully and the other with it returning an error. Do you have any thoughts as to why it would be calculating an error?

It's quite simple.

when you enter 10:30PM as time started and 1:00AM as Time finished, you know that 1:00AM is the day after but the computer is unaware of that.
And I was also unaware of the fact that you where able to treat this kind of values.
As I often wrote, when a question is not detailed enough, the response may be inaccurate.
When the question gives all needed details, the response may be accurate 😉

Now that I am aware, I may give a revised formula:

=IF(OR(ISBLANK(B),ISBLANK(C)),””,TIME(0,0,ROUND((IF(C>B,0,1)+TIMEVALUE(C4)-TIMEV ALUE(B4)) 24*6060,0)))

I also took care of the fact that you also apply the formula when one cell is not filled.

Yvan KOENIG (from FRANCE jeudi 31 juillet 2008 17:30:38)

In fact it would be better to compare timevalues not the contents of cells B and C so use:

=IF(OR(ISBLANK(B),ISBLANK(C)),"",TIME(0,0,ROUND((IF(TIMEVALUE(C)>TIMEVALUE(B),0, 1)+TIMEVALUE(C)-TIMEVALUE(B)) 24*6060,0)))

Yvan KOENIG (from FRANCE jeudi 31 juillet 2008 17:52:10)

This thread helped a lot so far ... I'm 1 week into being a MacBookPro user, and 1 day into iWork '08.

I'm trying to setup a time sheet to see (approximately) how many hours of work I've done. However, I seem to have run into a brick wall because some of my work days run over 24 hours. Using the Time(h,m,s) function only returns the time difference <24 hours.

This is my current Formula:

=IF($H5="Swing", TIME(0,0,ROUND((TIMEVALUE($G5)-TIMEVALUE($C5)) 24*6060,0)), IF($H5="Call", TIME(24,0,ROUND((TIMEVALUE($G6)-TIMEVALUE($C5)) 24*6060,0)), TIME(0,0,0)))




Date Time In Interns Residents Rounds Time Out Note Time
Aug 1, 2008 5:00 AM 5:30 AM 6:30 AM 9:00 AM 5:30 PM Swing 12:30
Aug 2, 2008 5:00 AM 5:30 AM 6:30 AM 9:00 AM - Call 5:00
Aug 3, 2008 - 5:30 AM 6:30 AM 9:00 AM 10:00 AM Post Call 0:00

In the example, August 2nd I went into work at 5AM and got out August 3rd 10AM, but the formula only tells me I worked 5 hours. The cells in the last column are formated as Date/Time with None under date and 14:10 under time.

Message was edited by: BigT_NYCOM

Question asked and responded several times.

Numbers states clearly in the Help and the PDF Users Guide that it doesn't know a "duration" object but a time one which is restricted to the range 00:00:0 to 23:59:59.

For durations we must fool it.



in column D the formula is:
=C-B
in column E the formula is
=TIME(0,0,ROUND(MOD(D,1) 24*6060,0))
in column F the formula is
=IF(D>=1,INT(D)& " day ","")&E
in column G the formula is
=IF(D<1,E,INT(D)*60+LEFT(E,2)&RIGHT(E,LEN(E)-2))

In column E as well as in column F, if the duration is greater than 24 hours, the result is not a time value but a string so, the value in column D may be useful which is the duration with the day as unit value.

+-+-+-+-+-+-+-+-+

Worried Life Blues 2008

+4. Discussions+

+Apple Discussions, launched in August, 2000, have grown rapidly in usage and features. The main features include personalization, subscription capabilities and email capabilities. _For information on how to use Discussions, please visit the Discussions Help Page_. Cookies should be enabled and an Apple ID account is required if you would like to contribute to the discussions.+

+Entering the Help and Terms of Use area you will read:+

+*What is Apple Discussions and how can it help me?*+
+
Apple Discussions is a user-to-user support forum where experts and other Apple product users get together to discuss Apple products. … You can participate in discussions about various products and topics, find solutions to help you resolve issues, ask questions, get tips and advice, and more.+
+_If you have a technical question about an Apple product, be sure to check out Apple's support resources first by consulting the application Help menu on your computer and visiting our Support site to view articles and more on our product support pages._+

+*I have a question or issue*—+
+how do I search for answers? _
It's possible that your question or issue has already been answered by other members so do a search before posting a question._ On most Apple Discussions pages, you'll find a Search Discussions box in the upper right corner. Enter a search term (or terms) in the field and press Return. Your results will appear as a list of links to posts below the Search Discussions Content pane.+

+Search tips are available here:+
+<a class="jive-link-external-small" href="http://">http://discussions.apple.com/help/search-tips.html+

+-+-+-+-+-+-+-+-+

Yvan KOENIG (from FRANCE lundi 4 août 2008 09:27:05)

Hello

I forgot to explain that with your described values, it is not necessary to use TIMEVALUE() because the date information is given in the main cells.

Yvan KOENIG (from FRANCE lundi 4 août 2008 10:37:18)

KOENIG Yvan wrote:
Numbers states clearly in the Help and the PDF Users Guide that it doesn't know a "duration" object but a time one which is restricted to the range 00:00:0 to 23:59:59.

When I search the U.S. language Numbers User Guide for the word "duration," it is not found. Searching for "datetime" returns only one reference, to the SECOND function; for "time," "date," or "date-time" there are many references, but it isn't exactly easy to find where this bit of information about the date-time data type is explained. In fact, there are many references to 'date or time' values, as if these were two separate data types.

The info is in the Guide, but saying that it is clearly stated might be a bit too optimistic.

KOENIG Yvan wrote:
Numbers states clearly in the Help and the PDF Users Guide that it doesn't know a "duration" object but a time one which is restricted to the range 00:00:0 to 23:59:59.

When I search the U.S. language Numbers User Guide for the word "duration," it is not found.

What may be more clear: _duration is not available but time is_?

Once again your response resemble to a rant againt the Help and the User Guide.

In the Help:

+date-time Any Numbers date/time value. _While you can choose to display only date or time in a cell, all Numbers date or time values contain both the date and time._+

Which wording would be more clear and precise?

TIMEVALUE

+The TIMEVALUE function converts a date, a time, or a text string to _a decimal fraction of a 24-hour day._+

Which wording would be more clear and precise?

TIME

+The TIME function converts hours, minutes, and seconds into a time format.+
+TIME(hours, minutes, seconds)+
+hours: The number of hours _(using a 24-hour clock)._+
+minutes: The number of minutes.+
+seconds: The number of seconds.+
Notes
+You can specify hour, minute, and second values greater than 23, 59, and 59, respectively. _If the hours, minutes, and seconds add up to more than 24 hours, Numbers subtracts 24 hours repeatedly until the sum is less than 24 hours._+

Which wording would be more clear and precise?

In the User Guide:

page 190

+date-time Any Numbers date/time value. _While you can choose to display_+
+_only date or time in a cell, all Numbers date or time values contain_+
+_both the date and time._+

+TIME (page 277) Converts a time to a decimal fraction of a 24-hour day.+
+TIMEVALUE (page 278) Converts a time in a string to a decimal fraction of a 24-hour day.+

TIME
+The TIME function converts the specified time to a decimal fraction of a 24-hour day.+
+TIME(hours, minutes, seconds)+
+• hours: The number of hours _(using a 24-hour clock)_.+
+• minutes: The number of minutes.+
+• seconds: The number of seconds.+
Notes
+You can specify hour, minute, and second values greater than 23, 59, and 59,+
+respectively. _If the hours, minutes, and seconds add up to more than 24 hours,_+
+_Numbers subtracts 24 hours repeatedly until the sum is less than 24 hours._+
+You can also specify fractional values for hours, minutes, or seconds.+

TIMEVALUE
+The TIMEVALUE function converts a time in a string to a decimal fraction of a 24-hour+
day.
TIMEVALUE(date-time)
+• date-time: A date, a time, or a string in any of the Numbers date and time formats.+

As you may check, the infos are exactly the same in the Help and in the Guide.
And I really don't understand how you may find them unclear.

Yvan KOENIG (from FRANCE lundi 4 août 2008 14:57:36)

I have no interest in debating how clear the wording is. I have only pointed out that it is a source of confusion for some users. If it is perfectly clear to you, that is wonderful, but that does not mean the same is true for everyone.

Maybe they must learn how to read.

Yvan KOENIG (from FRANCE lundi 4 août 2008 18:04:33)