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

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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

B
  • Question Question
Excel VBA Searching Unique Variables to Match against Exported Schedule Files to Determine Date
Replies
1
Views
190
sijpie
sijpie
D
  • Solved
Calculating the difference between 2 time values using VBA
Replies
8
Views
150
Micron
M
N
  • Solved
Help with formula please, if two values match calculate the difference between 2 dates
2
Replies
11
Views
473
Newbie73
N
E
  • Solved
Add together figures for Year-To-Date (with criteria)
Replies
3
Views
210
Tetra201
Tetra201
C
  • Solved
Lookup multiple criteria and find match within mixed alpha numerical cells and return the value matched
2 3
Replies
21
Views
917
Chrissy_M
C

Forum statistics

Threads
1,224,918
Messages
6,181,735
Members
453,064
Latest member
robatthe2A

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