Adding and Subtracting TIME with more than two values

Question

mac-a-rooney Author

Level 1

9 points

Adding and Subtracting TIME with more than two values

Hi there,

I want to calculate working time and travel time. Therefore, I've set um a NUMBERS spreadsheet with

Column A = Time leaving the house

Column B = Time arriving at work = Start of working time

Column C = End of working time = Time leaving work place

Column D = Time arriving back home

Column E = Break (or other non-working time)

to give a concrete numeric example lets assume the following dates (time format is hh:mm):

Column A: 8:00

Column B: 8:45

Column C: 18:00

Column D: 18:30

Column E: 00:45

Now I want to calculate the pure working time in Column F. I tried to do so by using the formula

=Column C - Column B - Column E (i.e. finishing work - starting work - break = net working time)

Column G is supposed to return the pure travel time. I tried this formula

=(Column D - Column C) + (Column B - Column A) (i.e. (arriving at home - leaving work place) + (arriving at workplace - leaving the house) so simply the two drives to and from work summed up)

However, in both columns I receive error messages. The messages saying "the operator "-" can not subtract a date from a number". But all my cells are formatted as dates. No number is involved and all cells used to calculate columns F and G use "pure" entries, so do not use cells which content calculated values.

Column H is supposed to subtract one hour from traval time. I'd expect to be able to simply use Column G and deduct one hour approximately like that:

=Column G - 01:00 but since I have already the error message in Column G, there is no chance to test this.

Finally Column I should return values after using comparison operators such as in the following

=if(Column F < 05:00, "nothing", if(Column F < 08:00, 6, 12))

so if net working time is less than 5 hours "nothing" should be returned in Column I. If net working time is between 8 and 5 hours the value 6 should be returned. And for all net working times longer than 8 hours the value 12 is supposed to display.

But also here, I have no chance to test because the initial error in Column F.

Sorry for the long description. I hope the clarity of the problem makes it worth, though. Would be more than happy to get any help on this, and insights about how to deal better with time calculations.

Thanks a lot.

mbp 13", Mac OS X (10.6.3)

Posted on Mar 9, 2012 3:09 AM

Reply

Answer 1

Top-ranking reply

Jerrold Green1

Level 7

30,247 points

Mar 9, 2012 8:15 PM in response to mac-a-rooney

MaR,

You wrote:

=Column C - Column B - Column E (i.e. finishing work - starting work - break = net working time)

The first subtraction yields a Duration, then you proceed to try to subtract a Date from that Duration. That's why it fails.

Once you get up to speed on Date/Time and Duration data types, you'll be able to handle the other calculations.

Since this Date/Time and Duration business can be confusing, I think your best bet is to convert all your Date/Time values to fractions of a day by usnig TIMEVALUE, prior to doing the math.

Regards,

Jerry

Reply

Answer 2

Barry

Level 9

59,951 points

Mar 10, 2012 1:13 AM in response to mac-a-rooney

HI mac...

Here's a go at your sample data, using pretty much the approach you describe. Some notes on what changes were necessary to make it work follow the image. All notes refer to row 2.

Columns A-D contain Date and Time values, entered as the time of day shown in each of the cells. Numbers sets the D&T value as the entered time on the date which it was entered. The cells are formatted to show only the Time part of the D&T value, and to show it as hours and minutes on a 24 hour clock.

Columns E-H contain Duration values. I've used the format shown as this format makes it clear that the values are Durations, and not Time of Day entries. The value in E was entered as shown. The others are calculated values, using formulas listed below. Formatting on these cells (and on column I) was left as 'Automatic'.

Column I contains a numerical value (or could contain the text value 'nothing'), and is determined by a formula.

Formulas

F2: =(C-B)-E

Essentially similar to your version. C and B are Time values, and can be subtracted. E is a Duration value. Enclosing the first subtraction in parentheses ensures that the result (a Duration) is used in the second subtraction.

G2: =(B-A)+(D-C)

Again, the parentheses ensure that the result of the first subtraction (a Duration) and the result of the second subtraction (another Duration) are what is used for the addition. The result is a Duration value.

H2: =G-DURATION(,,1,,,)

This is where the iWork Formulas and Functions User Guide, and the Function Browser become essential tools. The formula is subtracting a Duration of 1 hour from the Duration value in G2. The two commas preceding the 1 must be included. Those following the 1, the last argument given a value in the function, may be omitted.

I2: =IF(DUR2HOURS(F)<5,"nothing",IF(DUR2HOURS(F)<8,6,12))

Again, a trip to the F&F guide and the Function Browser are helpful. The issue is the same as in the previous formula: a duration value can't be directly written into a formula, Here, though, the opposite approach was taken.

DUR2HOURS(F) converts the Duration value in F2 to a number representing the number of hours (and fraction of an hour) in the Duration. That number is compared with 5, then if necessary, with 8 in the two IF statements, and the formula returns the appropriate result.

Regards,

Barry

Reply

Answer 3

mac-a-rooney Author

Level 1

9 points

Mar 15, 2012 11:05 AM in response to Barry

Hi Barry,

thank you very much. Indeed, your answer was very helpful, not only to solve my problem but to deal with time and/ or duration calculations in general!

One really minor thing I do not totally agree with you, though, is the your line

"Numbers sets the D&T value as the entered time on the date which it was entered."

For me, when I enter a time, it is always set as the time on the 1st January of the current year. Although this might also be due to the mobile version of numbers that I use for this particualr spreadsheet as well...?

By the way: I'd like to ask you to reply to this thread one more time since I've ranked your answer only as "helpful" and I think that doesn't give you the amount of points that your answer deserved 🙂

Reply

Answer 4

Mar 15, 2012 11:51 AM in response to mac-a-rooney

mac-a-rooney wrote:

Hi Barry,

thank you very much. Indeed, your answer was very helpful, not only to solve my problem but to deal with time and/ or duration calculations in general!

One really minor thing I do not totally agree with you, though, is the your line

"Numbers sets the D&T value as the entered time on the date which it was entered."

For me, when I enter a time, it is always set as the time on the 1st January of the current year. Although this might also be due to the mobile version of numbers that I use for this particualr spreadsheet as well...?

By the way: I'd like to ask you to reply to this thread one more time since I've ranked your answer only as "helpful" and I think that doesn't give you the amount of points that your answer deserved 🙂

M-A-R,

That is an interesting bit of information for us here in the OS X iWork discussion area. I rather like the change and wonder if it will become universal. As it is, there could be quite a bit of confusion when versions aren't specified, as was the case here, originally.

Jerry

Reply

Answer 5

Barry

Level 9

59,951 points

Mar 15, 2012 11:54 AM in response to mac-a-rooney

"For me, when I enter a time, it is always set as the time on the 1st January of the current year. Although this might also be due to the mobile version of numbers that I use for this particualr spreadsheet as well...?"

Interesting. I wonder if that might be a tweak added to Numbers (for iOS) after feedback regarding unexpected results for users not paying attention to the fact that all times include a date component. Setting 'unspecified' dates to a common value would eliminate issues arising from subtracting times (of the same day) entered on different calendar days. It still leaves the 'midnight boundary' issue in place, though, so I'm not sure I'd call it a step forward.

Regards,

Barry

Reply