calculate repair days

[Bruno_x]

I have column A with the date that a power tool has been reported as "to repair" and column B with the day the repaired tool was returned
I have to calculate the number of days that the machine was not present. But...

The broken machines are only collected on Tuesdays and Thursdays
So, a machine reported on thursday, friday or monday will be collected on the next tuesday. A machine reported on tuesday or wednesday will be collected the next thursday.

I can use 7 nested IF formulas, but there might be a better way to calculate the number of days...

Book1
	E	F	G
23	report	return	days
24	2/02/18	13/02/18	7
25	3/02/18	13/02/18	7
26	4/02/18	15/02/18	9
27	5/02/18	13/02/18	7
28	6/02/18	13/02/18	5
29	7/02/18	15/02/18	7
30	8/02/18	20/02/18	7
31	9/02/18	20/02/18	7
32	10/02/18	20/02/18	7
33	11/02/18	21/02/18	8
34	12/02/18	20/02/18	7
35	13/02/18
36	14/02/18
37	15/02/18
Blad1

 

[MartyS]

Hi Bruno,


Try this:


Excel 2010

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Reported "To Repair"[/TD]
[TD="align: center"]Repaired Tool Returned[/TD]
[TD="align: center"]Days[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]Fri 02/02/2018[/TD]
[TD="align: right"]Tue 13/02/2018[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]Sat 03/02/2018[/TD]
[TD="align: right"]Tue 13/02/2018[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]Sun 04/02/2018[/TD]
[TD="align: right"]Thu 15/02/2018[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]Mon 05/02/2018[/TD]
[TD="align: right"]Tue 13/02/2018[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]Tue 06/02/2018[/TD]
[TD="align: right"]Tue 13/02/2018[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]Wed 07/02/2018[/TD]
[TD="align: right"]Thu 15/02/2018[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]Thu 08/02/2018[/TD]
[TD="align: right"]Tue 20/02/2018[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]Fri 09/02/2018[/TD]
[TD="align: right"]Tue 20/02/2018[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]Sat 10/02/2018[/TD]
[TD="align: right"]Tue 20/02/2018[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]Sun 11/02/2018[/TD]
[TD="align: right"]Wed 21/02/2018[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]Mon 12/02/2018[/TD]
[TD="align: right"]Tue 20/02/2018[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]Tue 13/02/2018[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]Wed 14/02/2018[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]Thu 15/02/2018[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF(B2>0,B2-A2-VLOOKUP(TEXT(A2,"ddd"),{"Fri",4;"Mon",1;"Sat",3;"Sun",2;"Thu",5;"Tue",2;"Wed",1},2,FALSE),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "][/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Bruno_x]

Thanks Marty for your reply

Translating the formula in the dutch version took me some time, specially the part between {}
> VERT.ZOEKEN(TEKST(A5;"ddd"); {"ma"\1;"di"\2;"wo"\1;"do"\5;"vr"\4;"za"\3;"zo"\2};2;ONWAAR);"")

all works fine

 

[MARK858]

Translating the formula in the dutch version took me some time, specially the part between {}

This might be of some future use...

https://en.excel-translator.de/translator/

:wink:

 

[MartyS]

Glad it helped..

Dutch Excel.. Fascinating!

 

[markmzz]

Another way:

=IF(B2="","",B2-A2-LOOKUP(WEEKDAY(A2),{1;2;3;4;5;6;7},{2;1;2;1;5;4;3}))<strike></strike><strike></strike>

Markmzz

 

[Bruno_x]

This might be of some future use...

https://en.excel-translator.de/translator/

:wink:

translate the functions > no big problem and the use semi-colons in stead of comma's is also known
The link doesn't translate the commas and semi-colons in this part: {"Fri",4;"Mon",1;"Sat",3;"Sun",2;"Thu",5;"Tue",2;"Wed",1} to the backslash "" which is needed in the dutch version

 

[MARK858]

A bit surprised that the backslash is needed in the Dutch version as it normally signifies to take the next character as a literal and even more surprised that no-one has raised it as an issue with Mourad as the Dutch version has been around for a long time (and he lives on the border of Germany and Holland).

Can you just confirm that this is always the case before I put it to him or is it only in arrays?

 

[Bruno_x]

This is the first time i use vlookup this way, i mean with an array inside the formula; I usually use vlookup with a lookup-table like most people do
I've found the info https://support.office.com/nl-nl/article/matrixformules-richtlijnen-en-voorbeelden-7d94a64e-3ff3-4686-9372-ecfd5caa57c7
search for : "Eendimensionale en tweedimensionale constanten maken" > Create one-dimensional and two-dimensional constants"
btw i live in Flanders

 

[MARK858]

btw i live in Flanders

I know I could see it from your location I am just a bit surprised that no-one has raised it (to my knowledge) with Mourad.

I will ask if there is anything he can do with it although I suspect it isn't going to be an easy task as (in the version we use in the UK at least, see the link below) with the examples posted they come up as commas and semi-colons and so he might have real difficulty pin pointing exactly when it needs to happen (or rather when it is not to happen).


https://support.office.com/en-ie/ar...formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7

Anyway I will put it to him and see.