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

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: 24
  • Gantt Mrexcel.JPG
    Gantt Mrexcel.JPG
    54.5 KB · Views: 25

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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...

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

Forum statistics

Threads
1,223,923
Messages
6,175,391
Members
452,640
Latest member
steveridge

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