second formula calculation

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

Reply

Answer 1

Top-ranking reply

To_Mi

Level 4

1,202 points

Mar 28, 2017 1:53 AM in response to limmy3

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

Reply

Answer 2

To_Mi

Level 4

1,202 points

Mar 31, 2017 10:28 AM in response to limmy3

Hi limmy3,

I have trouble to figure out the relationship between O7 and K7.

So, I just put focus on the formula for the regulation.

The regulations say that between 23 o'clock and 6 o'clock in the morning the worker gets 25% plus if the work is more than 2 hours and above but no more than 7 hours in total.

The part "no more than 7 hours" is easy as MIN( 7, value ) .

The other part is a bit difficult. The formula will be IF(value<=2,0,value).

The latter needs two references to the value, so it might be better have another assistant column to store it.

X7 = original formula for O7
O7 = IF(X7<2,0,MIN(7,X7))

# I'm looking for better way but haven't get one yet.

Or you can take alternative way if column O can hold the value before the adjustment.

# this is tricky. you may not like it.

O7 = keep unmodified, but give custom data format to show it as 0 for 2 or less and 7 for 7 or more. ( sample in below)
Reference to O7 in formula on other column : replace it with IF(O7<2,0,MIN(7,O7))
Sum of column O value [ e.g. SUM(O) ] : replace it with SUNIFS(O,O,">=2",O,"<7")+7*COUNTIF(O,">=7)

or just apply 7-hour cap to O7 then use a bit less tricky reference to data on column O.

O7 = MIN(7, (original-formula for O7))
Reference to O7 : replace it with IF(O7<2,0,O7)
Sum of column O value : replace it with SUMIFS(O,O,">=2)

Reply

Answer 3

Yellowbox

Level 7

21,373 points

Mar 21, 2017 5:43 AM in response to limmy3

Guten abend limmy,

Good evening limmy,

My German is not good. I am using Google Translate.

Please tell me if this is correct.

I am not using Header Rows, nor Header Columns, nor Merged Cells.

Is this what you want to do?

Grüße aus Australien,

Ian.

Reply

Answer 4

limmy3 Author

Level 1

8 points

Mar 22, 2017 4:41 AM in response to To_Mi

Hello To_Mi,

thank you for your answer.

to explain the complexity I need to say I’m a taxi driver. I have fun in making these tables for my own purpose ( an hobby). And I do not do it for work only.

Well, as an none trained numberist ;-) I gather complex formulas and set them for my purpose.

Hi limmy3,

Sorry, I couldn't get your point yet.

1

What the meaning of each columns, at least for those needs to calculate the value for column J?

Please look at the pictures.

2

Whether those columns' value are entered manually or calculated from others? What is the formula if it is latter? ( seemingly, the cells in gold are entered manually but not sure )

Yes that is correct the yellow cells are manually entered.

3

What is the problem with your formula? I guess the first half of the formula is to get hours the standby time after driving, but almost no idea for the last half.

This is the same table as you have answered before with

column L. Column (C) is the overall time when I start to work.

Column (D) is then relevant when I first go on standby and

start driving later, e.g. Start work at 17 hrs but start/do

driving from e.g. 19 hrs untill 23 hrs. which shows in

column (E) 4 h.

Column (J) shall calculate the time on standby without driving

minus the time in column (K) (which is the driving time during

the standby time). When in column (E) the times ends at e.g. at

1,25 hrs in the morning the time to 6 hrs is 4,75 h of driving.

Now sometimes it happens that I need to drive from perhaps

3,25 hrs to 3,75 hrs which would be an half an hour and this

time should minus in column (J) (Column (K) is a collection of times but done manually).

As my working time ends at 6 hrs in the morning (I love it compicate ;-) ) but sometimes

again it happens that I do have to drive to the aiport e.g. from

4,25 hs to 6,75 hrs which means that the 0,75 minutes from

6 hrs to 6,75 hrs should not be in column J. These 0,75 minutes

should be added in column (N) together with the result of (F) minus (G) plus (K).

Complicated ? Yes indeed but easier than what I tried before.

Got it ? ;-)

4

Second picture in the original post shows starting standby/working (B) as 18 o'clock but start working (C)as

17, which is 1 hour earlier than B. Is it correct?

Sorry, off course this is a mistake which I’ve overseen.

It will be helpful, if you provide info about these.

Sorry, off course I have been a bite lazy

P.S.

I have tried your language trick but nothing happened.

WENN(UND(D7=0;E7=0;H7=0;I7=0);−0;WENN(H7<I7;I7−H7;24+I7−H7))+WENN(UND(D7>6;E7=I7 );−24;−0)

WENN(UND(I7≥6;F7<13);E7;0)

WENN(UND(D7=0;D7=0);0;WENN(D7<E7;E7−D7;24+E7−D7))

WENN(LÄNGE(C7)×LÄNGE(D7)×LÄNGE(E7)=0;"";MAX(MIN(D7;22);6;C7)−MAX(6;C7)+WENN(UND( D7<E7;E7<22);22−E7;0))

That is the formula you have made for me two days before.

J7−K7

F7+K7−G7

WENN(LÄNGE(E7)×LÄNGE(I7)=0;"";MAX(MIN(I7;23);7;E7)−MAX(7;E7))+WENN(E7>23;E7−23;2 4+E7−23)

This last formula is an extra calculation for work (driving time ) done from 23 hours to 6 hours in the morning only. Times driven before 23 hours and after 6 hours will not be calculated.

I'm trying to solve this but my limited knowledge make it rather difficult. If you've possibly got a solution for this I would be so thankful.

I have copied all formulas just that you can get an idea of what I'm trying to achieve.

Sorry again that I could not change the language.

So To_Mi if you are the one, and off course all the other nice Fellows in this community, who love this challenge I will be so thankful for your help. I don't know if it is possible to inset working tables in this community

For everyone here is a link to dropbox to download this table:

https://www.dropbox.com/s/ig8tutwqi7pty0s/Community%20table.numbers?dl=0

Thank you! :-)

Reply

Answer 5

limmy3 Author

Level 1

8 points

Mar 24, 2017 6:29 AM in response to limmy3

Hello To_Mi,

now after checking your formula without finding serious mistakes I can say you have done a great work. I'm sure the last difficulties I'll find, I'll managed.

Otherwise there's enough space on this discussion forum. ;-)

So this is the sheet I was woking on for so long. But I did it your way and hidden the auxiliary calculation.

So it looks smart!

Thank you very much.

Regards limmy3

Reply

Answer 6

To_Mi

Level 4

1,202 points

Mar 31, 2017 1:36 AM in response to To_Mi

A post to follow up switching language.

The method in the previous post to works for almost any application ( as far as it supports multiple language ).

But, I found Numbers 4.1 has mush easier way.

Go "File>Advanced>Language & Region..." where you can choose the language and region setting to use for the spread sheet.

# This setting affects only the document. Other documents and Numbers itself (menu, etc.) are NOT affected.

Reply

Answer 7

limmy3 Author

Level 1

8 points

Mar 31, 2017 3:26 AM in response to To_Mi

Hello To_Mi,

may I possibly ask you perhaps?

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)))

How can I adjust this formula that the value is only issued when it is in total ≥2 hours but off course between 23 h and 6 h and no more then 7 hours? Below 2 hours than 0.

Thus when the work in O7 is more then 2 hours than add K7.

The regulations say that between 23 o'clock and 6 o'clock in the morning the worker gets 25% plus if the work is more than 2 hours and above but no more than 7 hours in total.

So I think if I work after 23 o'clock for more than 2 hours K7 should be added if no but K7 is more than 2 hours then it should be added. If (23 to 6) ≥ 2 ; + K7; +IF K7 ≥2)

Complicated . ;-) I love it!

Thank you!!

Reply

Answer 8

limmy3 Author

Level 1

8 points

Apr 1, 2017 1:53 AM in response to To_Mi

Hello To_Mi,

the relationship between O7 and K7.

E7 "End drive" gives the time when I go from active driving in standby. When later than e.g. 23 o'clock than I get plus 25% in O7 if more than 2 hours of driving (that's how it should be). When I drive until e.g. 1.25 without having been on standby before or between I will receive these 25% from 23 o'clock to 1.25 o'clock. So in K7 I will only put the time when I have actually ended driving and went into standby but had to get out again because a costumer ask for a taxi.

I'm sorry this is rather difficult to explain when you are not used to it. I hope this is better explained.

I think you can forget the "no more than 7 hours" because the time is restricted from 23 o'clock to 6 o'clock thus it gives the 7 hours restriction.

Sorry I made it so complicated. Thus the focus is the limitation that I will get 25% only if 2 hours and more of driving have been made between 23 o'clock to 6 o'clock.

I have hoped that could be the answer.

IF(OR($C8="",$D8="",$E8="",$I8=""),"",IF(AND(MAX(0,MIN(6,R8))−MAX(0,Q8))+MAX(23, MIN(30,R8))−MAX(23,MIN(30,Q8)),(R8−23)≥2,+K8))

Your original O7 formula works fine it is only this restriction if more than 2 hours has to be found.

As I said before for me it is rather testing and trying before knowing what I'm doing.

Have a nice weekend!

Regards limmy3

And thank you!

Reply

Answer 9

To_Mi

Level 4

1,202 points

Mar 21, 2017 9:43 AM in response to limmy3

Hi limmy3,

Sorry, I couldn't get your point yet.

What the meaning of each columns, at least for those needs to calculate the value for column J?
Whether those columns' value are entered manually or calculated from others? What is the formula if it is latter? ( seemingly, the cells in gold are entered manually but not sure )
What is the problem with your formula? I guess the first half of the formula is to get hours the standby time after driving, but almost no idea for the last half.
Second picture in the original post shows starting standby/working (B) as 18 o'clock but start working (C) as 17, which is 1 hour earlier than B. Is it correct?

It will be helpful, if you provide info about these.

P.S.

The below is my method to run an app in different language setting than the primary language setting without third party tool.

My primary is Japanese, but do this occasionally to post messages in English (U.S.) here.

1. Open [Language&Region] pane in [System Preferences]

2. Bring the temporal language to top of the list. ( keep the pane open. Don't switch or close )

3. Invoke the app and wait it to be active.

4. Go back to [System Preferences] and bring the temporal language to the original position in the list.

5. Close [System Preferences]. As the primary language is set back to original, there will be no dialog asking to restart.

Reply

Answer 10

limmy3 Author

Level 1

8 points

Mar 21, 2017 5:52 AM in response to Yellowbox

Hello Ian,

thank you for your reply but I'm sorry I need a formula.

Something like this: 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)

Your table has no calculation!

Thank you

Regards limmi3

P.S. Have I been so misunderstanding in explaining?

Reply

Answer 11

Yellowbox

Level 7

21,373 points

Mar 21, 2017 6:15 AM in response to limmy3

Hello limmi,

Your table has no calculation!

That is because I do not understand your overall aim 😉.

Please place your document on Dropbox and post a link in a reply here. It is easy for me and other helpers to change System Preferences > Language & Region and read the document.

Happy Numbering!

Ian.

Reply

Answer 12

limmy3 Author

Level 1

8 points

Mar 23, 2017 6:44 AM in response to To_Mi

Hello To_Mi,

thank you so much for this help.

I appreciate it very much. Now I need some time to check everything.

It looks very good and I will reply if everything is as my idea was.

I have made it exactly the way you told me.

Later at work I will have some time to analyse it.

P.S. I managed to switch the language. Now I need to do it more often.

Thank you

Regards

limmy3

Reply

Answer 13

To_Mi

Level 4

1,202 points

Apr 1, 2017 9:28 PM in response to limmy3

OK, how about this?

U : standby time during 23-6 ( similar to M, but which is for 22-6 )

U8 = IF(OR($C8="",$D8="",$E8="",$I8=""),"",MAX(0,MIN(6,$Q8))−MAX(0,MIN(6,$P8))+MAX(0,MIN(6,$S8))−MAX(0,MIN(6,$R8))+MAX(23,M IN(30,$Q8))−MAX(23,MIN(30,$P8))+MAX(23,MIN(30,$S8))−MAX(23,MIN(30,$R8)))

T : actual driving time during 23-6 with K as adjustment ( original O is in bold )

Add K to original O, but the value to add must be equal or less than U.

T8 = IF(OR($C8="",$D8="",$E8="",$I8=""),"",MAX(0,MIN(6,$R8))−MAX(0,MIN(6,$Q8))+MAX(23,MIN(30,$R8))−MAX(23,MIN(30,$Q8))+MINA(K8,U8))

Note: MIN() ignores cells without number, but MINA() treats them as 0.

As you can see, U8 is referred only once. So, U8 in this formula can be replaced with the formula for T8 but it makes full formula very long and hard to maintain.

T8 = IF(OR($C8="",$D8="",$E8="",$I8=""),"",MAX(0,MIN(6,$R8))−MAX(0,MIN(6,$Q8))+MAX(23,MIN(30,$R8))−MAX(23,MIN(30,$Q8))+MINA(K8,MAX(0,MIN(6,$Q8))−MAX(0,MIN(6,$P8))+MAX(0,MIN(6,$S8))−MAX(0,MIN(6,$R8)) +MAX(23,MIN(30,$Q8))−MAX(23,MIN(30,$P8))+MAX(23,MIN(30,$S8))−MAX(23,MIN(30,$R8)) ))

However, if the value K is guarantied to be within the standby time during 23-6, there is no needs for U8.

In the case, the formula can be simplified as below.

T8 = IF(OR($C8="",$D8="",$E8="",$I8=""),"",MAX(0,MIN(6,$R8))−MAX(0,MIN(6,$Q8))+MAX(23,MIN(30,$R8))−MAX(23,MIN(30,$Q8))+MINA(K8))

O: driving time ( during 23-6 ) for extra charge.

valid on 2 hours or more but must not exceed 7 hours.

O8 = IF(T8="","",IF(T8<2,0,MIN(7,T8)))

If the use of T8 is not preferred, the first T8 can be replaced with OR part of T8, but other two in this formula must be replaced with the main part of formula for T8.

It will be also a nightmare on maintenance even with the shortest version, in my opinion.

Reply

Answer 14

limmy3 Author

Level 1

8 points

Apr 3, 2017 9:33 AM in response to To_Mi

Hello To_Mi,

absolutely great work!

Thank you vey much for this huge help.

Now everything works very well.

I can do my own working time calculations with these tables high precision.

"It will be also a nightmare on maintenance even with the shortest version, in my opinion."

No, that was not a nightmare. I copied the formula and coloured the several parts differently so I don't get confused.

T8 B = IF(OR($C8="",$D8="",$E8="",$I8=""),"",MAX(0,MIN(6,$R8))−MAX(0,MIN(6,$Q8))+MAX(23,MIN(30,$R8))−MAX(23,MIN(30,$Q8))+MINA(K8,MAX(0,MIN(6,$Q8))−MAX(0,MIN(6,$P8))+MAX(0,MIN(6,$S8))−MAX(0,MIN(6,$R8)) +MAX(23,MIN(30,$Q8))−MAX(23,MIN(30,$P8))+MAX(23,MIN(30,$S8))−MAX(23,MIN(30,$R8))))

Then made the formula and had a good orientation with the colours. Just fine.

Besides, after I made the work to fill in the formula part after part I played with the copy function.

When you use the right row number (Row 8) and copy your formula without '=' you simply copy and paste it in the formula box.

Could've saved me time and mistakes.

Thank you indeed!

Regards limmy3

Reply

Answer 15

To_Mi

Level 4

1,202 points

Mar 22, 2017 7:46 AM in response to limmy3

Thank you limmy3,

The info and file are great help.

I hope I can post solution soon.

P.S.

For language trick, did you quit Numbers before changing the language setting?

Reply