Numbers time calculations

Hi lgp,

Subtract the two times to get a Duration.

Formula in B2 (and Fill Down)

=B$1−A2

Be aware that in Numbers, when you enter a Time, it always has a Date attached (even if the Cell Data Format hides the Date, it is still there).

So in A9, entering 6:30 on 12 Dec, B9 will show the number of minutes as 1 day less 30 minutes (because B1 was entered on 11 Dec). Therefore:

Regards,

Ian.

.

Hello

You may try something like this.

Table 1 A1 time A2 2015-12-11 06:30:00 A3 2015-12-11 07:30:00 A4 2015-12-11 00:00:00 A5 2015-12-11 23:00:00 A6 B1 h:m till 7:00 B2 =DURATION(,TIMEVALUE(TIME(7,0,0))-TIMEVALUE(A2)) B3 =DURATION(,TIMEVALUE(TIME(7,0,0))-TIMEVALUE(A3)) B4 =DURATION(,TIMEVALUE(TIME(7,0,0))-TIMEVALUE(A4)) B5 =DURATION(,TIMEVALUE(TIME(7,0,0))-TIMEVALUE(A5)) B6 =DURATION(,TIMEVALUE(TIME(7,0,0))-TIMEVALUE(A6))

* Table is built with Numbers v2.

Regards,

H

Hi lgp007,

What do you want to display if the result is positive and greater than or equal to 2 hours?

This should work:

C2: =IF(B2<DURATION(,,,,0),DURATION(0),IF(B2>=DURATION(,,2),"???",B2))

Filled down the rest of column C

The error message is shown in the cells attempting to compare the duration 0s with a number (0) or an empty cell (which many functions interpret as 0 (zero).

Replace B2 with your calculation and "???" with what you want displayed if the result is NOT less than 2h.

Regards,

Barry

lgp007 wrote:

*I was wondering if it were possible to have a calculation that when a positive result is given that is less than 2hrs to display that time but if it's a negative then just to display 0 as i don't want the negative durations in the selected cells below?

You can also consider:

=MAX(DURATION(0),<your existing expression that sometimes results in negative duration>)

SG

Hi lgp,

Note the error message at the bottom of your first screen shot: You can't compare a date and time value with a duration because these data types are different.

In Numbers, there is no such thing as a 'pure' Date value or a 'pure' Time value.

Every 'Date' is a Date and Time value that includes a Date part and a Time part.

Every 'Time' is a Date and Time value that includes a Date part and a Time part.

If you enter a date from the keyboard, Numbers will set the time part of the resulting Date and Time value to 00:00:00 (midnight, at the beginning of that day), and will display only the Date part (unless you format the cell to display both parts).

If you enter a time of day from the keyboard, Numbers will set the Date part of the resulting Date and Time value to the date on which that time was entered, and will display only the time part (unless you format the cell to display both parts).

If you construct the time of day part using the TIME(h,m,s), the date part of the result is set to January 1, 1904 (the first day of the first leap year in the 20th century).

In the formula shown in your second screen shot, the comparison in the first IF statement is: B2 ≥ TIME(5,0,0)

If the time in B2 was entered on Sunday, December 13, then the Date/Time value in that cell is Dec 13, 2015 12:00AM

The Date/Time value created by the TIME function in the formula is Jan 1, 1904 5:00 AM.

The comparison Dec 13, 2015 12:00AM ≥ Jan 1, 1904 5:00 AM will evaluate as TRUE, as will any time entered into column B on any date in the 21st century.

I'll take a closer look at the rest of the formula later today—too far into the 00:00 - 7:00 period for close observation tonight.

Regards,

Barry

Two things: An extension to SGIII's formula to include a maximum value (2h) for the calculation to which it's applied, and a closer look at the calculation itself.

From SG's post:

=MAX(DURATION(0),existing expression)

Revised: Above formula enclosed in MIN(value 1,value 2)

=MIN(MAX(DURATION(0),existing expression),DURATION(,,2))

The existing formula from your second screen shot (I've used the address of cell B2 in place of the name in your screen shot, and shortened the name of the Admin table in references to the cell on that table to make the formula more compact here.):

C2: =MAX(DURATION(0),IF(B2≥TIME(5,5,0),IF(ISBLANK(Ad::$E$1)-ISBLANK(B2), ISBLANK(Ad::$E$1)-ISBLANK(B2), (Ad::$E$1)-(B2)))

Here's the same formula broken into parts, and with the parts labelled:

1: =MAX(

2: DURATION(0),

3: IF(

4: B2≥TIME(5,5,0),

5: IF(

6: ISBLANK(Ad::$E$1)-ISBLANK(B2),

7: ISBLANK(Ad::$E$1)-ISBLANK(B2),

8: Ad::$E$1-B2

9: )

A: )

B: )

MAX(value 1, value 2)

Line 2 is value 1 for this function, Everything from line 3 to line A is value 2, line B is the closing bracket for MAX.

No problems with line 2.

3: IF(test, do if TRUE, do if FALSE)

Line 4 is the test for this IF. It compares the Date/Time value in B2 with the Date/Time value constructed by the TIME() function. This comparison will always return true here for the reasons discussed in my previous post.

Lines 5 to 9 is the do if true part of this IF. There is no do if FALSE part.

Line A is the closing bracket for the IF at line 3.

5: A second IF(test, do if TRUE, do if FALSE) to be done only if th test for the first IF returns TRUE

6: the test for this IF

ISBLANK returns the Boolean value TRUE if the tested cell is empty and FALSE if the cell contains data of any type.

The result of the second test is subtracted from the result of the first, with four possible results:

TRUE - TRUE (both cells empty), FALSE - FALSE (both cells have data) Both these cases will return a result of zero.

TRUE - FALSE (data in E1, no data in B2) Returns result of 1

FALSE - TRUE (data in B2, no data in E1) Returns result of -1

All four cases will also produce a blue warning triangle warning that "The formula uses a Boolean in place of a number."

IF interprets any number other than zero as TRUE, so if one tested cell is empty and the other has data, IF moves on to 'do if TRUE (line 7); if neither has data or if both have data, IF moves on to do if FALSE (line 8).

7: do if TRUE

Done only if the result of the test was 'true' Since this expression is the same as the test expression, it's result will always be 1 or -1.

8: do if FALSE

Done only if the test result was 'false'.

The expression subtracts the contents of B2 from the contents of Ad::E1.

9: Closing bracket for the second IF. Result is passed back to the first IF.

A: Closing bracket for the first IF. Result is passed back to MAX

B: Closing bracket for MAX. Result is returned to cell C2.

Does this formula give the results you are expecting (disregarding the return of values greater than 2h for the current version)? If not, do you see here what part needs to be changed to make it return the expected results?

Regards,

Barry

Hi lgp007,

Having gone back to your original question, and reviewed the formula you have tried, I think I may have away to achieve your desired result.

Some key considerations:

Administration::E1 likely contains a fixed Date/Time value. This means the Date part of the value has to be ignored or compensated for when that value is used in calculations that include Date/Time values entered as time-of-day on a different day.

The calculation in C2 uses the Date/Time value in Admin::E1 and the Date/Time value in B2. Both values must be present for the calculation to have any meaning.

Although you haven't yet confirmed this, you have given indications that the range of acceptable values for the result is 0h - 2h.

For the example, the time in Administration::E1 was entered as a time of day, and the entry was made on December 1, 2015. The value in E1 will contain BOTH parts of that Date/Time value, although the cell will likely be formatted to DISPLAY only the Time part.

The IN time in B2 of Main was entered on December 14, 2015. Only the time part was entered, and only the time art would normally be displayed, but the cell actually contains the entire Date/Time value, and that must be accounted for in any calculations.

Core calculation:

C2: =Admin::E1-B2

Revised to eliminate the date differences:

C2: =DURATION(,,,(TIMEVALUE(Admin::E1)-TIMEVALUE(B2))*1440)

TIMEVALUE considers only the Time part of a Date/Time value, and converts the time of day to a number representing the fraction of a 24 hour day that has passed since midnight at the start of that date. The difference between the two time values is multiplied by 1440, the number of minutes in a full day, and converted by the DURATION function to the equivalent duration. The cell containing the formula is formatted to show durations in hours and minutes.

Filtered to permit only values between 0h and 2h

C2: =MIN(MAX(DURATION(0),DURATION(,,,(TIMEVALUE(Admin::E1)-TIMEVALUE(B2))*1440)),DURATION(,,2))

Control added to carry out the calculation only if there is are Date/Time values in both Admin::E1 and cell B2. The formula attempts to extract a minute value from each of the Date/Time values in B2 and Admin::E1, then add the two values together. If

C2: =IF(OR(LEN(Admin::E1)<1,LEN(B2)<1),"",MIN(MAX(DURATION(0),DURATION(,,,(TIMEVALUE(Admin::E1)-TIMEVALUE(B2))*1440)),DURA TION(,,2)))

Finally, before filling the formula down column C, make the cell references to Admin::E1 Absolute references:

C2: =IF(OR(LEN(Admin::$E$1)<1,LEN(B2)<1),"",MIN(MAX(DURATION(0),DURATION(,,,(TIMEVA LUE(Admin::$E$1)-TIMEVALUE(B2))*1440)),DURATION(,,2)))

The last formula shown was entered into Main::C2 and filled down to the end of column C. "IN" times were entered as 7:30 (example) on December 14, 2015. Format was left at Automatic on all cells in column B except B7, where the format was set to display both the Date and Time parts of the value in the cell. The rest of the cells in column B displaying times also contain the December 14 date part.

The empty cell (B3) contains no data, and the control part of the formula successfully prevents calculation (which would result in an error) from taking place. Note that the control tests only for the presence of any kind of data. Cell B9 contains a text value, which the control accepts, but which causes an error when the core part of the formula attempts to process that data.

Let me know if this does what is needed.

Regards,

Barry