SOLVED

Timeline - Back to the Future

%3CLINGO-SUB%20id%3D%22lingo-sub-1586386%22%20slang%3D%22de-DE%22%3ETimeline%20-%20Back%20to%20the%20Future%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1586386%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3ESo%20far%20I%20have%20made%20a%20worksheet%20where%20I%20built%20up%20my%20timeline.%3C%2FSPAN%3E%20%3CSPAN%3EIn%20the%20installation%20of%20the%20formulas%20that%20I%20have%20only%20managed%20with%20the%20help%20of%20so%20many%20here.%3C%2FSPAN%3E%20%3CSPAN%3EIn%20the%20process%20of%20realization%2C%20some%20prolemes%20have%20emerged%20where%20I%20need%20your%20help%20with.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CSPAN%3EProblem%201)%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%3EThe%20middle%20service%20%3D%20service%20(G%26amp%3BH)%20deducts%20helped%20an%20hour%20in%20the%20timeline.%3C%2FSPAN%3E%20%3CSPAN%3EExample%20MA2%20%26amp%3B%20MA2.%3C%2FSPAN%3E%20%3CSPAN%3EMA1%20should%20be%20green%20at%206%3A00%20p.m.%20and%20MA2%20should%20be%20orange-brown%20at%208%3A00%20p.m.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CBR%20%2F%3E%3CSPAN%3EProblem%202)%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%3EIf%20no%20time%20is%20entered%20in%20the%20service%20cells%2C%20the%20timeline%20looks%20like%20MA3%20%26amp%3B%20MA4%20%26amp%3B%20MA5.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CBR%20%2F%3E%3CSPAN%3EProblem%203)%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%3EHow%20can%20I%20set%20the%20list%20(columns%20A%20to%20L)%20so%20that%20all%20MA%20%26amp%3B%20GL%20are%20the%20same%20%3CSPAN%20class%3D%22%22%3Esequence%3C%2FSPAN%3E%20as%20in%20the%20timeline.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%3E%3CSPAN%20class%3D%22%22%3EAny%20help%20is%20welcome%2C%20thx%20in%20advance%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%3E%3CFONT%20size%3D%222%22%3E*Consolation%20is%20given%20by%20heaven%2C%20from%20whom%20man%20is%20expected%20to%20be%20assisted%20and%20helped%20(%20Juda%20L%C3%B6b%20Baruch).%3C%2FFONT%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1586386%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1586957%22%20slang%3D%22de-DE%22%3ERE%3A%20Timeline%20-%20Back%20to%20the%20Future%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1586957%22%20slang%3D%22de-DE%22%3EAnyone%20who%20could%20give%20me%20food%20for%20thought%3F%20...%20at%20the%20end%20there%20is%20also%20coffee%20and%20cake%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1589289%22%20slang%3D%22de-DE%22%3ERE%3A%20Timeline%20-%20Back%20to%20the%20Future%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1589289%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHolger%2C%20many%2C%20many%20thanks%20for%20the%20thought%20impulse...%3C%2FP%3E%3CP%3Ereally%20great!!%3C%2FP%3E%3CP%3EI%20really%20wouldn't%20have%20come%20up%20with%20the%20idea%20with%20the%20time%20difference%20with%20the%20example%200.7500000000000001.%20Even%20if%20I%20have%20to%20admit%20this%20many%20people%20here%20have%20also%20tried%20to%20bludbeve.%3C%2FP%3E%3CP%3ENow%20I%20have%20realized%20it%20mentally!!!%3C%2FP%3E%3CP%3EHave%20the%20formula%20of%20your%20message%20been%20recorded%20and%20it%20will%20work!!!%3C%2FP%3E%3CP%3EFor%20everyone%20as%20a%20solution%3A%3C%2FP%3E%3CP%3E%3D(((%24E3-%24D3)%20%26lt%3B%26gt%3B%200)*(%24D3%26lt%3B%3DQ%242)*(%24E3%26gt%3B%3DQ-2))%2B(((%24H3-%24G3)%20%26lt%3B%26gt%3B%200)*(%24G3%26lt%3B%3DQ%242)*(%24H3%26gt%3B%3DQ-2))%2B(((%24K3-%24J3)%20%26lt%3B%26gt%3B%200)*(%24J3%26lt%3B%3DQ%242)*((%24K3%2B1)%26gt%3B%3DQ-2))%20%20%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again%20and%20greetings%20to%20the%20boss%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Regular Contributor

So far I have made a worksheet where I built up my timeline. In the installation of the formulas which I have only managed with the help of so many here. In the process of realization, some prolemes have emerged where I need your help with.

Problem 1)

The middle service = service (G&H) deducts half an hour in the timeline. Example MA2 & MA2. MA1 should also be green at 6:00 p.m. and MA2 should be orange-brown at 8:00 p.m.


Problem 2)

If no time is entered in the service cells, the timeline looks like MA3 & MA4 & MA5.


Problem 3)

How can I set the list (columns A to L) so that all MA & GL are the same sequence as in the timeline.

 

Any help is welcome, thx in advance

 

Nikolino

I know I don't know anything (Socrates)

*Trost gibt der Himmel, von dem Menschen erwartet man Beistand und Hilfe ( Juda Löb Baruch).

 

2 Replies
Highlighted
Anyone who could give me food for thought? ... at the end there is also coffee and cake
Highlighted
Best Response confirmed by Nikolino (Regular Contributor)
Solution

@Nikolino 

 

Holger, vielen, vielen Dank für den Denk Anstoß...

echt super!!

Auf die Idee mit der Zeitdifferenz  mit dem Beispiel 0,750000000000001, wäre ich echt nicht darauf gekommen. Auch wenn ich zugeben muss das so mancher hier auch versucht hat einzubläuen.

Nun habe ich es gedanklich realisiert!!!

Habe die Formel von deiner Nachricht eingespielt und es funktioniert!!!

Für alle als Lösungsansatz:

=((($E3-$D3)<>0)*($D3<=Q$2)*($E3>=Q$2))+((($H3-$G3)<>0)*($G3<=Q$2)*($H3>=Q$2))+((($K3-$J3)<>0)*($J3<=Q$2)*(($K3+1)>=Q$2))

 

Danke nochmals und Grüße an die Chefin

 

Nikolino

Ich weiß das ich nichts Weiß (Sokrates)