calculate repair days

Bruno_x

Active Member
Joined
Feb 17, 2002
Messages
491
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
EFG
23reportreturndays
242/02/1813/02/187
253/02/1813/02/187
264/02/1815/02/189
275/02/1813/02/187
286/02/1813/02/185
297/02/1815/02/187
308/02/1820/02/187
319/02/1820/02/187
3210/02/1820/02/187
3311/02/1821/02/188
3412/02/1820/02/187
3513/02/18
3614/02/18
3715/02/18
Blad1
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Hi Bruno,


Try this:


Excel 2010
ABC
1Reported "To Repair"Repaired Tool ReturnedDays
2Fri 02/02/2018Tue 13/02/20187
3Sat 03/02/2018Tue 13/02/20187
4Sun 04/02/2018Thu 15/02/20189
5Mon 05/02/2018Tue 13/02/20187
6Tue 06/02/2018Tue 13/02/20185
7Wed 07/02/2018Thu 15/02/20187
8Thu 08/02/2018Tue 20/02/20187
9Fri 09/02/2018Tue 20/02/20187
10Sat 10/02/2018Tue 20/02/20187
11Sun 11/02/2018Wed 21/02/20188
12Mon 12/02/2018Tue 20/02/20187
13Tue 13/02/2018
14Wed 14/02/2018
15Thu 15/02/2018

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
C2=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),"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
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 :)
 
Upvote 0
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
 
Last edited:
Upvote 0
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?
 
Upvote 0
Upvote 0
btw i live in Flanders
I know I could see it from your location :ROFLMAO: 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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top