GANTT schedule - Take value if between time and equipment criteria match

T

TMS123

New Member
Joined
Jul 20, 2022
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I need to make a GANTT schedule from the production with tanks etc. I cannot get the different formulas to work, when they do I will add it as conditional formatting in different colours.

  1. In D2 "Gannt Mrexcel.jpg" I want TRUE if C2 find a match in H:H "Raw data Mrexcel.jpg", also the date and time in D1 must be equal or between E:E and G:G in "Raw data Mrexcel.jpg"
  2. Then I want to index this I:I value into D2.
  3. Then I want to drag the formula vertical and horizontal.
What happens now is that I only get FALSE excel from when I remove the Match formula like this:
=IF(AND($C2=Raw!$H:$H;D$1>=Raw!$E:$E;D$1<=Raw!$G:$G);1;0)
Then I can get some TRUE values in one area on different rows, but wrong.

I tried to used this formula from an old thread, but without success, maybe I missed something. Please help!

1658392737353.png
 

Attachments

  • Raw data Mrexcel.JPG
    Raw data Mrexcel.JPG
    84 KB · Views: 17
  • Gantt Mrexcel.JPG
    Gantt Mrexcel.JPG
    54.5 KB · Views: 18

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
OK Tested the first formula from @Fluff with TRUE/FALSE instead of index with some progress, now it finds values, but it seems on the wrong rows again. Any ideas?? I do not fully master the AGGREGATE and MOD functions...

MRexcel gantt.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMAN
1DatumWeekWeekdayStartStoppStart timeStop timeEquipmentArtikelVolym Equipment2022-07-15 21:002022-07-15 22:002022-07-15 23:002022-07-16 00:002022-07-16 01:002022-07-16 02:002022-07-16 03:002022-07-16 04:002022-07-16 05:002022-07-16 06:002022-07-16 07:002022-07-16 08:002022-07-16 09:002022-07-16 10:002022-07-16 11:002022-07-16 12:002022-07-16 13:002022-07-16 14:002022-07-16 15:002022-07-16 16:002022-07-16 17:002022-07-16 18:002022-07-16 19:002022-07-16 20:002022-07-16 21:00
22022-07-2029300:2101:532022-07-20 00:212022-07-20 01:53OS 2123040LDate2022-07-15 20:00OS111#NUM!111111111111111111111
32022-07-2029300:0401:002022-07-20 00:042022-07-20 01:00VT22796,5LWeek52OS 2111#NUM!111111111111111111111
42022-07-1929223:1901:182022-07-19 23:192022-07-20 01:18OS329710LDaylördagVT111#NUM!111111111111111111111
52022-07-1929222:2022:572022-07-19 22:202022-07-19 22:57P646050L+minutes60FLX111#NUM!111111111111111111111
62022-07-1929222:1423:092022-07-19 22:142022-07-19 23:09OS 2513841L+days0
72022-07-1929221:0322:212022-07-19 21:032022-07-19 22:21P6610060L
82022-07-1929220:0622:142022-07-19 20:062022-07-19 22:14VT76398,1L
92022-07-1929219:3120:522022-07-19 19:312022-07-19 20:52OS 2820243L
102022-07-1929219:2120:122022-07-19 19:212022-07-19 20:12P696040L
Gantt
Cell Formulas
RangeFormula
P1P1=N2+(N5/1440)
Q1:AN1Q1=P1+($N$5/1440)
P2:AN5P2=IF(AGGREGATE(15,6,(ROW($I$2:$I$20930)-ROW($I$2)+1)/($H$2:$H$20930=$O2)/(MOD($F$2:$F$20930,1)<=MOD(P$1,1))/(MOD($G$2:$G$20930,1)>=MOD(P$1,1)),1),1,0)
B2:B10B2=WEEKNUM(A2,21)
C2:C10C2=WEEKDAY(A2,2)
F2:F10F2=A2+D2
G2:G10G2=IF(E2<D2,A2+1+E2,A2+E2)
N3N3=WEEKNUM(N1,21)
N4N4=TEXT(N1,"dddd")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AO4:FI4,P4:AN5,P2:FI3,P7Cell Value=1textNO
 
Upvote 0
Managed to get it working now, but not over different dates, how to integrate the INT function in this formula and aggregate on date as for the time? Now the formula works, but will return the values when the time criteria is correct not taking the date into consideration.

MRexcel gantt.xlsx
DEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
1StartStoppStart timeStop timeEquipmentArtikelVolym Equipment2022-07-14 01:002022-07-14 01:052022-07-14 01:102022-07-14 01:152022-07-14 01:202022-07-14 01:252022-07-14 01:302022-07-14 01:352022-07-14 01:402022-07-14 01:452022-07-14 01:502022-07-14 01:552022-07-14 02:002022-07-14 02:052022-07-14 02:102022-07-14 02:152022-07-14 02:202022-07-14 02:252022-07-14 02:30
200:2101:532022-07-14 00:0044756,002022-07-14 01:0044756,00OS123040LDate2022-07-14 01:00OS12233              
300:0401:002022-07-14 01:0544756,002022-07-14 01:1044756,00OS22796,5LWeek52OS245566              
423:1901:182022-07-14 01:1544756,002022-07-14 01:2044756,00OS329710LDaylördagVT788                
522:2022:572022-07-14 00:0044756,002022-07-14 01:0044756,00OS246050L+minutes5FLX                   
622:1423:092022-07-14 01:0544756,002022-07-14 01:1044756,00OS2513841L+days0
721:0322:212022-07-14 01:1544756,002022-07-14 01:2044756,00OS2610060L
820:0622:142022-07-14 00:0044756,002022-07-14 01:0044756,00VT76398,1L
919:3120:522022-07-14 01:0544756,002022-07-14 01:1044756,00VT820243L
1019:2120:122022-07-14 01:1544756,002022-07-14 01:2044756,00VT96040L
Gantt
Cell Formulas
RangeFormula
R1R1=P2+P6
S1:AJ1S1=R1+($P$5/1440)
R2:AJ5R2=INDEX($K$1:$K$10,AGGREGATE(15,6,(ROW($K$2:$K$10)-ROW($K$1))/($J$1:$J$10=$Q2)/(MOD($F$1:$F$10,1)<=MOD(R$1,1))/(MOD($H$1:$H$10,1)>=MOD(R$1,1)),1))
F3:F4F3=H2+((1/1440)*5)
H3:H4H3=F3+((1/1440)*5)
P3P3=WEEKNUM(P1,21)
P4P4=TEXT(P1,"dddd")
I2:I10,G2:G10G2=INT(F2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
R7Cell Value=1textNO
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Result from multiple criteria and range between two values
Replies
3
Views
357
rythmic_flow
R
S
  • Question
Counting the number of employees working between two times.
Replies
2
Views
492
stryped
S
E
  • Solved
Lookup value from range when name matches AND date falls within date range
Replies
4
Views
689
Alex Blakenburg
A
E
  • Solved
VBA - If match, fill in Date and time in cell
Replies
6
Views
399
Engalpengal
E
P
  • Question
**Add existing date & time to another cell with condition**
2
Replies
16
Views
302
psc1
P

Forum statistics

Threads
1,214,978
Messages
6,122,545
Members
449,089
Latest member
davidcom

Share this page

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