Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

User profile for user: anjitago1
anjitago1 Author
User level: Level 1
8 points

Need help with subtracting time in Numbers

I am creating a spreadsheet to check that we have been paid correctly


Job involves shifts which have set start and finish times e.g.. 6:45am to 2:30pm with a 30min break


regularly we get called in early and might start anywhere from 15min to 3 hours early or later.


In the spreadsheet I have

(start time) A1. 6:45

(finish time) A2. 14:30

(Break) A3. 0.5

(result) A4 7.25 using the formula =a2-a1-a3


The meal break is in decimal and converts the answer to decimal which is how i require it.


The problem occurs when I try to add in extra work time

using above table i then add in

(start time) B1. 6:45

(finish time) B2. 14:30

(Break) B3. (empty)

(result) B4 22.25 using the formula =(a2-a1-a3)-(B2-B1-B3)


It is converting the extra time to 15 instead of 0.25.

If the extra time is over one hour e.g. 5:30 to 6:45 then it appears to work


another problem is when i add in the extra break time I get error because I can't subtract number value from time


Thanks in advance for you help

Numbers-OTHER, OS X Mavericks (10.9.5)

Posted on Apr 24, 2015 3:24 AM

Reply
Question marked as Best reply
User profile for user: Hiroto
Hiroto
User level: Level 5
7,461 points

Posted on Apr 24, 2015 5:31 AM

Hello


I don't understand your second example but correct formula for calculating time span would be as follows.


User uploaded file



Table 1 A1 2015-04-24 06:45:00 A2 2015-04-24 14:30:00 A3 0.5 A4 =(TIMEVALUE(A2)-TIMEVALUE(A1)-A3/24)*24



Note that there's no time-only value but date-time value in Numbers. Time is always accompanied with date. Thus you need to extract the true time-only value by TIMEVALUE() function which returns a number as [time in seconds since midnight] / [24 * 3600].


(In the above example, the date parts of date-time values in A1:A2 happen to be the same because I have inputted them on the same day. But it won't when I for instance notice a typo in A1's value and fix it on other day. When the date parts of A1 and A2 are different, the formula A2-A1 will yield wrong value you won't expect.)



* Table is built with Numbers v2.


Regards,

H


EDIT: fixed typos.

View in context
4 replies
Question marked as Best reply
User profile for user: Hiroto
Hiroto
User level: Level 5
7,461 points

Apr 24, 2015 5:31 AM in response to anjitago1

Hello


I don't understand your second example but correct formula for calculating time span would be as follows.


User uploaded file



Table 1 A1 2015-04-24 06:45:00 A2 2015-04-24 14:30:00 A3 0.5 A4 =(TIMEVALUE(A2)-TIMEVALUE(A1)-A3/24)*24



Note that there's no time-only value but date-time value in Numbers. Time is always accompanied with date. Thus you need to extract the true time-only value by TIMEVALUE() function which returns a number as [time in seconds since midnight] / [24 * 3600].


(In the above example, the date parts of date-time values in A1:A2 happen to be the same because I have inputted them on the same day. But it won't when I for instance notice a typo in A1's value and fix it on other day. When the date parts of A1 and A2 are different, the formula A2-A1 will yield wrong value you won't expect.)



* Table is built with Numbers v2.


Regards,

H


EDIT: fixed typos.

Reply
User profile for user: anjitago1
anjitago1 Author
User level: Level 1
8 points

Apr 26, 2015 6:54 PM in response to Hiroto

Thanks Hiroto for you reply


Thanks for letting me know about "time value" Its something that i never knew and it would catch you out every time.


the second part of the table should be

B1. 6:30

B2. 6:45

B3. 0.01

B4. 22.24 (Expecting result of 7.49)


Formula in B4 =(A2-A1-A3)+(B2-B1-B3)


The time values in column A are from a lookup table and are selected via a drop down box

Time values in column B are entered in all at same time


Here is a sample table to give an idea of what i need

A1

B

C

D

2

Start time

6:45


3

Finish Time

14:30


4

Break

0.5


5

Extra start time

6:30


6

Extra finis time

6:45


7

extra break

0.01


8



formulas used

9

standard time

7.25

TIMEVALUE(B3−TIMEVALUE(B2+B4÷24))×24

10

total time

32.01

(TIMEVALUE(B3−TIMEVALUE(B2−B4÷24))×24)+(TIMEVALUE(B5−TIMEVALUE(B6−B7÷24))×24)


The problem I'm getting is Total Time should give me the result of 6.24 instead of 30.76

normal time is 6 hours for the shift

plus extra 15 minutes starting early and a .01 break just to check extra break time is working

total time answer should be 7.49

Reply
User profile for user: Hiroto
Hiroto
User level: Level 5
7,461 points

Apr 27, 2015 5:18 AM in response to anjitago1

Hello


You're using wrong formulae.


WRONG:


=TIMEVALUE(B3−TIMEVALUE(B2+B4÷24))×24 =(TIMEVALUE(B3−TIMEVALUE(B2−B4÷24))×24)+(TIMEVALUE(B5−TIMEVALUE(B6−B7÷24))×24)



CORRECT:


=TIMEVALUE(C3)−TIMEVALUE(C2)+C4/24))*24 =TIMEVALUE(C3)−TIMEVALUE(C2)+C4/24))*24+TIMEVALUE(C6)−TIMEVALUE(C5)+C7/24))*24



* Pay close attention to where to put parentheses. (Numbers v3 uses childish ÷ in lieu of professional / for arithmetic division and childish × in lieu of professional * for arithmetic multiplication. You may safely ignore these differences in v2 and v3.)





The table below may illustrate it more clearly.



User uploaded file



Table 1 A1 A A2 2 A3 3 A4 4 A5 5 A6 6 A7 7 A8 8 A9 9 A10 10 A11 11 B1 B B2 start time B3 finish time B4 break B5 etra start time B6 extra finish time B7 extra break B8 B9 standard time B10 extra time B11 total time C1 C C2 2015-04-24 06:45:00 C3 2015-04-24 14:30:00 C4 0.5 C5 2015-04-27 06:30:00 C6 2015-04-27 06:45:00 C7 0.01 C8 C9 =(TIMEVALUE(C3)-TIMEVALUE(C2)-C4/24)*24 C10 =(TIMEVALUE(C6)-TIMEVALUE(C5)-C7/24)*24 C11 =C9+C10




Regards,

H

Reply

Need help with subtracting time in Numbers

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.
Learn more Sign up