-
Expanding IF formula
I am using this formula to get data from the sheet 'Uge1', but only if no one of the cells 'Uge1!D4' and 'Uge1!E4' are empty.
Code:
=HVIS(ELLER(ER.TOM(Uge1!D4);(ER.TOM(Uge1!E4)));"";HVIS(MINUT(Uge1!D4)>0;TIME(Uge1!D4)&"."&MINUT(Uge1!D4);TIME(Uge1!D4))&"-"&HVIS(MINUT(Uge1!E4)>0;TIME(Uge1!E4)&"."&MINUT(Uge1!E4);TIME(Uge1!E4))&" "&Uge1!F4)
And here it is translated with my best school- english:
Code:
=IF(OR(IS.EMPTY(Uge1!D4);(IS.EMPTY(Uge1!E4)));"";IF(MINUTE(Uge1!D4)>0;HOUR(Uge1!D4)&"."&MINUTE(Uge1!D4);HOUR(Uge1!D4))&"-"&IF(MINUTE(Uge1!E4)>0;HOUR(Uge1!E4)&"."&MINUTE(Uge1!E4);HOUR(Uge1!E4))&" "&Uge1!F4)
Is it possible to expand the conditions so that if the cell 'Uge1!F4' is different from 'Rulleplan!D4', then target cell is not left empty (the part og formula after ;""; is getting the data)?
I have been trying this:
Code:
=IF(OR(IS.EMPTY(Uge1!D4);(IS.EMPTY(Uge1!E4)));Uge1!F4=Rulleplan!D4;"";IF(MIN ....
but get error.
-
Is this what you mean?
Code:
=HVIS(OG(ELLER(ER.TOM(Uge1!D4);ER.TOM(Uge1!E4)),Uge1!F4=Rulleplan!D4);"";
HVIS(MINUT(Uge1!D4)>0;TIME(Uge1!D4)&"."&MINUT(Uge1!D4);TIME(Uge1!D4))&"-"&HVIS(MINUT(Uge1!E4)>0;TIME(Uge1!E4)&"."&MINUT(Uge1!E4);TIME(Uge1!E4))&" "&Uge1!F4)
-
I've got an error using it.
AND(Logic1;[Logic2];...)
And part of the formula ",Uge1" was highlighted
-
Changed comma to semicolon, and it works.
Only now i get "0-0 700" (the "700" is what i want).
It seems that when Uge1!D4 and Uge1!E4 is empty, it results in zeroes.
I would like, when Uge1!D4 and Uge1!E4 is empty, but Uge1!F4 is different from Rulleplan!D4, only the content of Uge1!F4 is entered in the targetcell.
-
Sorry about the comma.
This assumes that you want the not equal test to take precedence
Code:
=HVIS(OG(Uge1!F4<>Rulleplan!D4,Uge1F4;OG(ELLER(ER.TOM(Uge1!D4);ER.TOM(Uge1!E4)));"";
HVIS(MINUT(Uge1!D4)>0;TIME(Uge1!D4)&"."&MINUT(Uge1!D4);TIME(Uge1!D4))&"-"&HVIS(MINUT(Uge1!E4)>0;TIME(Uge1!E4)&"."&MINUT(Uge1!E4);TIME(Uge1!E4))&" "&Uge1!F4))
-
Got some errors trying to use your formula. Tryed to change it like this:
Code:
=HVIS(OG(Uge1!F4<>Rulleplan!D4;OG(ELLER(ER.TOM(Uge1!D4);ER.TOM(Uge1!E4))));"";HVIS(MINUT(Uge1!D4)>0;TIME(Uge1!D4)&"."&MINUT(Uge1!D4);TIME(Uge1!D4))&"-"&HVIS(MINUT(Uge1!E4)>0;TIME(Uge1!E4)&"."&MINUT(Uge1!E4);TIME(Uge1!E4))&" "&Uge1!F4)
Worked just like before, except directly opposite (i got "0-0 700" when Uge1!F4 was equal to Rulleplan!D4) , so i chanced it to:
[VCode]=HVIS(OG(Uge1!F4=Rulleplan!D4;OG(ELLER(ER.TOM(Uge1!D4);ER.TOM(Uge1!E4))));" ";HVIS(MINUT(Uge1!D4)>0;TIME(Uge1!D4)&"."&MINUT(Uge1!D4);TIME(Uge1!D4)) &"-"&HVIS(MINUT(Uge1!E4)>0;TIME(Uge1!E4)&"."&MINUT(Uge1!E4);TIME(Uge1!E4)) &" "&Uge1!F4)[/Code]
and it worked just like your previos formula
-
If F4 <> Rulleplan!D4 AND neither D4 nor E4 is blank, should it show F4 or the concatenated D4/E4 result?
-
Maybe i can explain what i want another way.
This is the original formula:
Code:
=HVIS(ELLER(ER.TOM(Uge1!D4);(ER.TOM(Uge1!E4)));"";HVIS(MINUT(Uge1!D4)>0;TIME(Uge1!D4)&"."&MINUT(Uge1!D4);TIME(Uge1!D4))&"-"&HVIS(MINUT(Uge1!E4)>0;TIME(Uge1!E4)&"."&MINUT(Uge1!E4);TIME(Uge1!E4))&" "&Uge1!F4)
This part:
Code:
HVIS(ELLER(ER.TOM(Uge1!D4);(ER.TOM(Uge1!E4)));"";
determine that if Uge1!D4 or Uge1E4 is empty, the target cells should be empty too.
This part:
Code:
HVIS(MINUT(Uge1!D4)>0;TIME(Uge1!D4)&"."&MINUT(Uge1!D4);TIME(Uge1!D4))&"-"&HVIS(MINUT(Uge1!E4)>0;TIME(Uge1!E4)&"."&MINUT(Uge1!E4);TIME(Uge1!E4))&" "&Uge1!F4)
put data in target cell different ways depending on minutes is zeroes or not and then add the content of Uge1!F4.
What i want is, when Uge1!F4 is different from Rulleplan!D4 but Uge1!D4 or Uge1E4 is empty to put only the content of Uge1!F4 in the target cell.
-
Sorry for the look of my reply, tryed to make it look like this
-
How do you do that
Nihil sim
-
Yes, we know that, but that condition could be met when D4 and E4 are also not blank, so which takes precedence?
-
If Uge1!F4 = Rulleplan!D4 AND either Uge1!D4 or Uge1!E4 is blank, it should show blank
If Uge1!F4 = Rulleplan!D4 AND either Uge1!D4 or Uge1!E4 is blank, it should show only the content of Uge1!F4.
If both Uge1!D4 and Uge1!E4 is filled the rest of the code show the content of Uge1!D4 and Uge1!E4 and Uge1!F4
-
Code:
=HVIS(Uge1!F4<>Rulleplan!D4;Uge1!F4;HVIS(ELLER(ER.TOM(Uge1!D4);ER.TOM(Uge1!E4));"";
HVIS(MINUTE(Uge1!D4)>0;TIME(Uge1!D4)&"."&MINUTE(Uge1!D4);TIME(Uge1!D4))&"-"&HVIS(MINUTE(Uge1!E4)>0;TIME(Uge1!E4)&"."&MINUTE(Uge1!E4);TIME(Uge1!E4))&" "&Uge1!F4))
or, in English
Code:
=IF(Uge1!F4<>Rulleplan!D4,Uge1!F4,IF(OR(ISBLANK(Uge1!D4),ISBLANK(Uge1!E4)),"",
IF(MINUTE(Uge1!D4)>0,HOUR(Uge1!D4)&"."&MINUTE(Uge1!D4),HOUR(Uge1!D4))&"-"&IF(MINUTE(Uge1!E4)>0,HOUR(Uge1!E4)&"."&MINUTE(Uge1!E4),HOUR(Uge1!E4))&" "&Uge1!F4))
-
If "Uge1!F4<>Rulleplan!D4" is true the formula now shows only Uge1!F4 even if data is entered in both Uge1!D4 and Uge1!E4.
If i use:
Code:
=HVIS(OG(Uge1!F4=Rulleplan!D4;OG(ELLER(ER.TOM(Uge1!D4);ER.TOM(Uge1!E4))));"";
HVIS(MINUT(Uge1!D4)>0;TIME(Uge1!D4)&"."&MINUT(Uge1!D4);
TIME(Uge1!D4))&"-"&HVIS(MINUT(Uge1!E4)>0;TIME(Uge1!E4)&"."&MINUT(Uge1!E4);
TIME(Uge1!E4))&" "&Uge1!F4)
and both Uge1!D4 and Uge1!E4 is blank the formula shows "0-0 700", where the zeroes an the - comes from Uge1!D4 and Uge1!E4.
Maybe the rest of the formula should be changed. The first part seemes to do what it is supposed to do.
Code:
HVIS(MINUT(Uge1!D4)>0;TIME(Uge1!D4)&"."&MINUT(Uge1!D4);TIME(Uge1!D4))&"-"&HVIS(MINUT(Uge1!E4)>0;TIME(Uge1!E4)&"."&MINUT(Uge1!E4);
TIME(Uge1!E4))&" "&Uge1!F4)
-
I have been fiddling with the formula, this is what i have until now:
Code:
=HVIS(OG(Uge1!F4=Rulleplan!D4;OG(ELLER(ER.TOM(Uge1!D4);ER.TOM(Uge1!E4))));"";
HVIS(MINUT(Uge1!D4)>0;TIME(Uge1!D4)&"."&MINUT(Uge1!D4);HVIS(TIME(Uge1!D4)>0;(TIME(Uge1!D4))&"-")
&HVIS(MINUT(Uge1!E4)>0;TIME(Uge1!E4)&"."&MINUT(Uge1!E4);HVIS(TIME(Uge1!E4)>0;(TIME(Uge1!E4))
&HVIS(ER.TOM(Uge1!F4);"";HVIS(ELLER(ER.TOM(Uge1!D4);(ER.TOM(Uge1!E4)));Uge1!F4;" /"&Uge1!F4))))))
If i enter data in Uge1!D4 AND Uge1!E4 the result is precisely what i want, whether i change Uge1!F4 or not.
If i do not enter data in Uge1!D4 AND Uge1!E4 and change Uge1!F4 i get FALSEFALSE in the targetcell.
If i do not enter data in Uge1!D4 AND Uge1!E4 and do not change Uge1!F4 i get blank in the targetcell (of course).
Hope it helps, it did not help me.
-
You've made a right mess of that haven't you?
-
-
Compile a list of all possible variations of Uge1!D4 Blank/Not Blank, Uge1!F4 Blank/Not Blank, and Uge1!F4=/<>Rulleplan!D4 (I make that 8 variations), and then say what the result should be. I'll take a look tomorrow then.
-
I will try.
The workbook is calculating workinghoures for the employees and give a visible look at the covering of needed employees.
The cells we are working on here is for 1 employee (me).
U1!D4 and U1!E4 is in the format [t]:mm
U1!F4 is standardformat
Target cell is standardformat
I have made a little change (non destructive :think: ) in the formula so it looks like this:
Code:
=HVIS(ELLER(ER.TOM(Uge1!D4);(ER.TOM(Uge1!E4)));"";_
HVIS(MINUT(Uge1!D4)>0;TIME(Uge1!D4)&"."&MINUT(Uge1!D4);TIME(Uge1!D4))&"-"&HVIS(MINUT(Uge1!E4)>0;TIME(Uge1!E4)&"."&MINUT(Uge1!E4);TIME(Uge1!E4))_
&HVIS(ER.TOM(Uge1!F4);"";HVIS(ELLER(ER.TOM(Uge1!D4);(ER.TOM(Uge1!E4)));Uge1!F4;" /"&Uge1!F4)))
Here is the list (RP! = Rulleplan!:
Uge1!D4=Empty | Uge1!E4=Empty | Uge1!F4=RP!D4 | Target cell = Empty
Uge1!D4=Empty | Uge1!E4=Empty | Uge1!F4<>RP!D4 | Target cell = Uge1!F4
Uge1!D4=Filled | Uge1!E4=Empty | Uge1!F4=RP!D4 | Target cell = Empty
Uge1!D4=Filled | Uge1!E4=Empty | Uge1!F4<>RP!D4 | Target cell = Uge1!F4
Uge1!D4=Empty | Uge1!E4=Filled | Uge1!F4=RP!D4 | Target cell = Empty
Uge1!D4=Empty | Uge1!E4=Filled | Uge1!F4<>RP!D4 | Target cell = Uge1!F4
Uge1!D4=Filled | Uge1!E4=Filled | Uge1!F4=RP!D4 | Target cell = Uge1!D4&-&Uge1!E4
Uge1!D4=Filled | Uge1!E4=Filled | Uge1!F4<>RP!D4 | Target cell = Uge1!D4&-&Uge1!E4& /&Uge1!F4
Example:
Rulleplan!D4=700
Uge1!D4 [Empty] | Uge1!E4 [Empty] | Uge1!F4 [700] | Target Cell [Empty]
Uge1!D4 [Empty] | Uge1!E4 [Empty] | Uge1!F4 [900] | Target Cell [900]
Uge1!D4 [7:00] | Uge1!E4 [Empty] | Uge1!F4 [700] | Target Cell [Empty]
Uge1!D4 [7:00] | Uge1!E4 [Empty] | Uge1!F4 [900] | Target Cell [900]
Uge1!D4 [Empty] | Uge1!E4 [15:00] | Uge1!F4 [700] | Target Cell [Empty]
Uge1!D4 [Empty] | Uge1!E4 [15:00] | Uge1!F4 [900] | Target Cell [900]
Uge1!D4 [7:00] | Uge1!E4 [15:00] | Uge1!F4 [Empty] | Target Cell [7-15]
Uge1!D4 [7:00] | Uge1!E4 [14:30] | Uge1!F4 [Empty] | Target Cell [7-14.30]
Uge1!D4 [7:00] | Uge1!E4 [15:00] | Uge1!F4 [700] | Target Cell [7-15 /700]
Uge1!D4 [7:00] | Uge1!E4 [14:30] | Uge1!F4 [900] | Target Cell [7-14.30 /900]
The part of the formula that makes the U1!F4-part of the targetcell look different
("900" or "7-14.30" or "7-14.30 /900") is this:
&HVIS(ER.TOM(Uge1!F4);"";HVIS(ELLER(ER.TOM(Uge1!D4);(ER.TOM(Uge1!E4)));Uge1 !F4;" /"&Uge1!F4)))
Tried to make the listings look like tables. Not easy in here
-
Here we go then my friend
Code:
=HVIS(ELLER(ER.TOM(Uge1!D4);ER.TOM(Uge1!E4));HVIS(Uge1!F4=Rulleplan!D4;"";Uge1!F4);
TEKST(Uge1!D4;HVIS(MINUT(Uge1!D14)=0;"h";"h:mm"))&"-"&
TEKST(Uge1!E4;HVIS(MINUT(Uge1!E4)=0;"h";"h:mm"))&
HVIS(ER.TOM(Uge1!F4);"";" /"&Uge1!F4))
In English
Code:
=IF(OR(ISBLANK(Uge1!D4),ISBLANK(Uge1!E4)),IF(Uge1!F4=Rulleplan!D4,"",Uge1!F4),
TEXT(Uge1!D4,IF(MINUTE(Uge1!D14)=0,"h","h:mm"))&"-"&
TEXT(Uge1!E4,IF(MINUTE(Uge1!E4)=0,"h","h:mm"))&
IF(ISBLANK(Uge1!F4),""," /"&Uge1!F4))
You might need to change the Text formats, I use h and H:mm for hours and hours and minutes. Danish might be other letters.
-
I was just about to show you that the formula did not do what it was supposed to do, i got som reel strange results.
Then i saw your last line, changed the h and H:mm to t and t:mm and that is it.
It works :clap:
Thank You :thumb
Some day i will stand like this again :banghead:
Then i will just remember that there are people like You and i will end up like this :hi:
-
I should have figured that, becuase I recall that Danish for Hour is Time isn't it? Mea culpa.
