You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

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

limmy3 Author

Level 1

8 points

second formula calculation

Hello fellows,

after a nice guy solved a big problem with a formula which I could not fix.

There is the next issue to be solved.

WENN(UND(D3=0;E3=0;H3=0;I3=0);−0;WENN(H3<I3;I3−H3;24+I3−H3))+WENN(UND(D3>6;E3=I3 );−24;F3−I3+F3)

Column J is the formula which has to be fixed.

Thus I would like to ask you for help.

Column C is beginning of the standby and driving time (working time). In Column D the driving time starts. In column E the driving time ends. The driving and standby time ends in column I actually the working time. The result would be in column J. The working and standby time are usually from 17 hours in the evening to 6 hours in the morning. In column 3 the month starts from midnight and the work ends at 6:45 in the morning that's why D3 has 0,0001 as start time. Column J calculates the time from column E to the end usually. In line 3 the result would be J3 6,75 hours of work.

My own try:

In line 3 the start time in D3 should be 0 (0,0001) hours midnight (C3 to E3 was just a test). The results in J3, J5, J6 are as they should be.

Formular in J5 is: WENN(LÄNGE(C5)×LÄNGE(D5)×LÄNGE(E5)=0;"";WENN(H5<I5;I5−H5;24+I5−H5))+WENN(UND(D5 >6;E5=I5);−24;−0)

Translation: wenn = if, und = and, länge = length. In German time system you have the 24 hours.

If any one is interested : column "O" should calculate the time from 23 hours at night to 6 hours in the morning. For this column I have not made a formula yet. Column "E" is start and "I" end time. The result in "O4" is 7 hours.

Possibly I'll have a try on this later. If some one is not quicker.

So I hope you have all the information you need to solve the formulas. For me it's when it comes to the difficult formulas more an experimenting rather then experience or knowledge.

Thank you in advance for any one you tries his luck.

Regards limmy3

iMac, OS X El Capitan (10.11), always latest version

Posted on Mar 20, 2017 1:14 PM

Top-ranking reply

To_Mi

Level 4

1,202 points

Posted on Mar 28, 2017 1:53 AM

Hi limmy3,

I believe I made the table.

The reason the formula complicated is 6 means 6:00 of the day for day-1 but 6:00 of the next day for other days.

So, I added 4 columns of data.

P3 = C3
Q3 = D3 + IF(D3<C3,24,0)
R3 = E3 + IF(E3<C3,24,0)
S3 = I3 + IF(I3<C3,24,0)

These values make formulas simple.

# IF(OR(....),"", is just to check data is entered.

# So, only the part in bold is necessary to get value if no checking is required.

F3 = IF(OR(D3="",E3=""),"",$R3−$Q3)
J3 = IF(OR(C3="",D3="",E3="",I3=""),"",$Q3−$P3+$S3−$R3−K3)
L3 = IF(OR(C3="",D3="",E3="",I3=""),"",MAX(6,MIN(22,$Q3))-MAX(6,MIN(22,$P3))+MAX(6,MIN(22,$S3))-MAX(6,MIN(22,$R3))+MAX( 30,MIN(46,$Q3))-MAX(30,MIN(46,$P3))+MAX(30,MIN(46,$S3))-MAX(30,MIN(46,$R3)))

Does it look complicated? the logic is simple.

1. get overwrap between 6--22 and P--Q (standby before driving)

2. get overwrap between 6--22 and R--S (standby after driving)

3. get overwrap between 30--46 (6--22 of next day) and P--Q

4. get overwrap between 30--46 and R--S

5. get total of 1,2,3, and 4.

# [3] may be omitted but not sure. So, I put it there to make formula symmetric.

# For L3,

The same goes for M3, overwrap between [ 0--6, 22--30 ] and [ P--Q ], and for O3, overwrap between [ 0--6, 23--30 ] and [ P--Q ]

M3 = IF(OR(C3="",D3="",E3="",I3=""),"",MAX(0,MIN(6,$Q3))−MAX(0,MIN(6,$P3))+MAX(0,MIN(6,$S3))−MAX(0,MIN(6,$R3))+MAX(22,M IN(30,$Q3))−MAX(22,MIN(30,$P3))+MAX(22,MIN(30,$S3))−MAX(22,MIN(30,$R3)))
O3 = IF(OR(C3="",D3="",E3="",I3=""),"",MAX(0,MIN(6,$R3))−MAX(0,MIN(6,$Q3))+MAX(23,MIN(30,$R3))−MAX(23,MIN(30,$Q3)))

# As you can see, MAX(0,MIN(6,X)) can be replaced with MIN(6,X) as X will not be negative value.

# I guess these don't need to handle for 22:00 next day or later

You can make data check simple.

add another column, say T, and use it for data readiness.

T3 = OR(C3="",D3="",E3="",I3="")

then formulas with data check will be like,

X3 = IF($T3,"", .... )

Hope it works.

Appendix:

Formulas in Germany

Q3 = D3+WENN(D3<$C3;24;0)
R3 = D3+WENN(E3<$C3;24;0)
S3 = I3+WENN(I3<$C3;24;0)
F3 = WENN(ODER($R3="";$Q3="");"";$R3−$Q3)
L3 = WENN(OR(C3="";D3="";E3="";I3="");"";MAX(6;MIN(22;$Q3))-MAX(6;MIN(22;$P3))+MAX(6 ;MIN(22;$S3))-MAX(6;MIN(22;$R3))+MAX(30;MIN(46;$Q3))-MAX(30;MIN(46;$P3))+MAX(30; MIN(46;$S3))-MAX(30;MIN(46;$R3)))
M3 = WENN(OR(C3="";D3="";E3="";I3="");"";MAX(0;MIN(6;$Q3))−MAX(0;MIN(6;$P3))+MAX(0;M IN(6;$S3))−MAX(0;MIN(6;$R3))+MAX(22;MIN(30;$Q3))−MAX(22;MIN(30;$P3))+MAX(22;MIN( 30;$S3))−MAX(22;MIN(30;$R3)))
O3 = WENN(OR(C3="";D3="";E3="";I3="");"";MAX(0;MIN(6;$R3))−MAX(0;MIN(6;$Q3))+MAX(23; MIN(30;$R3))−MAX(23;MIN(30;$Q3)))

Logic I made the table for ( as I understand )

C	start working time	P	= C
:	(standby)
D	start driving time	Q	= D + IF(D<C,24,0)
:	(driving)
E	end driving time	R	= E + IF(E<C,24,0)
:	(standby)
I	end working time	S	= I + IF(I<C,24,0)

F	driving hours : D<->E
G	break hours during driving ?
H	start time of standby after driving = E ?
J	standby hours, subtract K
K	adjustment for J

L	standby hours in 6 <-> 22
M	standby hours in 22 <-> 6
N	actual driving hours = F - G + K
O	driving hours in 23 <-> 6