Vlookup Question

[UKFoolsGold]

Hi All,

I hope someone can help me to save my sanity!

I have created a spreadsheet which is basically a shut plan,from the shut plan a Gantt chart is created using conditional formatting.

Because the shut plan needs to be hourly over a 3-week span,the Gantt chart was tricky for me (I’m pretty average on excel).
The start/finish times of jobs are in dd/mm/yyyy hh:hhformat as they are extracted from an asset management system and that is theformat they are in when they are extracted.
The way I thought I could get the Gantt chart working overindividual day/hours for 3 weeks was to assign each hour a unique number. Example below:

<tbody> [TD="width: 130, bgcolor: transparent"]

Date​

[/TD]
[TD="width: 85, bgcolor: transparent"] Reference [/TD]
[TD="width: 130, bgcolor: transparent"]

01/02/2019 00:00​

[/TD]
[TD="width: 85, bgcolor: transparent"]

1​

[/TD]
[TD="width: 130, bgcolor: transparent"]

01/02/2019 01:00​

[/TD]
[TD="width: 85, bgcolor: transparent"]

2​

[/TD]
[TD="width: 130, bgcolor: transparent"]

01/02/2019 02:00​

[/TD]
[TD="width: 85, bgcolor: transparent"]

3​

[/TD]
[TD="width: 130, bgcolor: transparent"]

01/02/2019 03:00​

[/TD]
[TD="width: 85, bgcolor: transparent"]

4​

[/TD]
[TD="width: 215, bgcolor: transparent, colspan: 2"]

And so on…​

[/TD]
</tbody>

This reference data is kept on a separate sheet called “data”,there is one table for start time (columns A & B) and one table for finishtime (columns D & E)
When the start & finish dates/times are entered, avlookup formula should return the unique reference, which would then populatethe Gantt chart.
I have the Gantt chart working ok, but the vlookup formulais acting strangely, and I wonder if somebody could help. It looks something like below:

[TABLE="width: 537"]
<tbody>[TR]
[TD="width: 28, bgcolor: transparent"]

1​

[/TD]
[TD="width: 224, bgcolor: transparent"]

A​

[/TD]
[TD="width: 76, bgcolor: transparent"]

B​

[/TD]
[TD="width: 85, bgcolor: transparent"]

C​

[/TD]
[TD="width: 84, bgcolor: transparent"]

D​

[/TD]
[TD="width: 68, bgcolor: transparent"]

E​

[/TD]
[TD="width: 75, bgcolor: transparent"]

F​

[/TD]
[TD="width: 75, bgcolor: transparent"]

G​

[/TD]
[/TR]
[TR]
[TD="width: 28, bgcolor: transparent"]

2​

[/TD]
[TD="width: 224, bgcolor: transparent"]

Job Description​

[/TD]
[TD="width: 76, bgcolor: transparent"]

WO Number​

[/TD]
[TD="width: 85, bgcolor: transparent"]

Start Time​

[/TD]
[TD="width: 84, bgcolor: transparent"]

Finish Time​

[/TD]
[TD="width: 68, bgcolor: transparent"]

Duration​

[/TD]
[TD="width: 75, bgcolor: transparent"]

Unique Start Ref​

[/TD]
[TD="width: 75, bgcolor: transparent"]

Unique Finish Ref​

[/TD]
[/TR]
[TR]
[TD="width: 28, bgcolor: transparent"]

3​

[/TD]
[TD="width: 224, bgcolor: transparent"]

Repair temp gauge​

[/TD]
[TD="width: 76, bgcolor: transparent"]

1​

[/TD]
[TD="width: 85, bgcolor: transparent"]

01/02/2019 06:00​

[/TD]
[TD="width: 84, bgcolor: transparent"]

01/02/2019 09:00​

[/TD]
[TD="width: 68, bgcolor: transparent"]

3​

[/TD]
[TD="width: 75, bgcolor: transparent"]

​

[/TD]
[TD="width: 75, bgcolor: transparent"]

​

[/TD]
[/TR]
[TR]
[TD="width: 28, bgcolor: transparent"]

4​

[/TD]
[TD="width: 224, bgcolor: transparent"]

​

[/TD]
[TD="width: 76, bgcolor: transparent"]

​

[/TD]
[TD="width: 85, bgcolor: transparent"]

​

[/TD]
[TD="width: 84, bgcolor: transparent"]

​

[/TD]
[TD="width: 68, bgcolor: transparent"]

​

[/TD]
[TD="width: 75, bgcolor: transparent"]

​

[/TD]
[TD="width: 75, bgcolor: transparent"]

​

[/TD]
[/TR]
[TR]
[TD="width: 28, bgcolor: transparent"]

5​

[/TD]
[TD="width: 224, bgcolor: transparent"]

​

[/TD]
[TD="width: 76, bgcolor: transparent"]

​

[/TD]
[TD="width: 85, bgcolor: transparent"]

​

[/TD]
[TD="width: 84, bgcolor: transparent"]

​

[/TD]
[TD="width: 68, bgcolor: transparent"]

​

[/TD]
[TD="width: 75, bgcolor: transparent"]

​

[/TD]
[TD="width: 75, bgcolor: transparent"]

​

[/TD]
[/TR]
</tbody>[/TABLE]
​

• The formula I have in F3 is ‘=VLOOKUP(C2,Data!$A$1:$B$480,2,TRUE)’
  
• The formula I have in G3 is ‘=VLOOKUP(D2,Data!$D$1:$E$480,2,TRUE)’

I thought this formula was working ok but then I noticedthat sometimes the Vlookup would return a unique reference number maybe onenumber wrong here and there.
Now, I believe the TRUE statement returns an approximatematch, I initially had it set as FALSE but the start time formula wouldn’t workeven thought the finish time formula would which is confusing me.
I had a go at trying an index match formula but couldn’t getit working either.
Could anybody on here help me with this, either with gettingthe Vlookup working accurately or even with an index match formula that woulddo the job.
Thanks
UKFoolsGold

 

[AlanY]

to work out the duration, instead of the vlookup what you needed is to subtract the start from the finish time

e.g.

E3 = (D3-C3)*24

 

[UKFoolsGold]

Hi Alan,
Thanks for the reply, the duration isn’t the problem, thatworks out by using a simple formula similar to the one you stated.
The Vlookup returns are used to auto populate a Gantt chart,the gantt chart uses the formula =AND($F3<=J$1,$G4>=J$1), obviously thisis just a single cell extract of the formula.
Apologies for the confusion.
Thanks
UKFoolsGold

 

[AlanY]

this is based on the vlookup(...,false) that works, could you provide some data the are not working here for us to test

Book1
	A	B	C	D	E	F	G
1	Date	Reference
2	01/02/2019 00:00	1
3	01/02/2019 01:00	2
4	01/02/2019 02:00	3
5	01/02/2019 03:00	4
6
7
8	Job Description	WO Number	Start Time	Finish Time	Duration	Unique Start Ref	Unique Finish Ref
9	Repair temp gauge	1	01/02/2019 00:00	01/02/2019 03:00	3	1	4
10
Sheet1

Cell Formulas
Range	Formula
F9	=VLOOKUP(C9,$A$2:$B$5,2,0)
G9	=VLOOKUP(D9,$A$2:$B$5,2,0)

 

[UKFoolsGold]

Hi Alan,

I've attached some screens below with your formula inserted.

Hope this helps.

Thanks

UKFoolsGold

 

[AlanY]

don't ask, it's a pain to attach image.

are you ok to share it on a public storage, e.g. google drive?

 

[AlanY]

it's the way excel to store the date/time to 15 decimal places (or thereabout), one way to solve that is to rounded them up to the same format, e.g. =ROUND(..,5)

here is an example

https://drive.google.com/file/d/1x50q6lDmLwVmMn03pp50DBjDzAa4NL5x/view?usp=sharing

 

[AlanY]

back to your original question, you can rid of the vlookup() with the formula in cols F & G

Book1
	A	B	C	D	E	F	G
2	Job Description	WO Number	Start Time dd/mm/yyyy hh:hh	Finish Time dd/mm/yyyy hh:hh	Duration hours	Start Time (Hour No)	Finish Time (Hour No)
3
4	Fix light	1	03/05/2019 07:00	03/05/2019 10:00	3:00	8	10
5	Fix pump	2	03/05/2019 08:00	03/05/2019 17:00	9:00	9	17
6	Fix spectrometer receiver	3	03/05/2019 09:00	03/05/2019 18:00	9:00	10	18
7	Fix leak on gland	4	04/05/2019 06:00	04/05/2019 18:00	12:00	31	42
8	Clean pump strainer	5	04/05/2019 09:00	04/05/2019 17:00	8:00	34	41
Gantt Chart

Cell Formulas
Range	Formula
F4	=(C4-DATE(2019,5,3))*24+1
G4	=(D4-DATE(2019,5,3))*24

 

[UKFoolsGold]

Thanks for that Alan, that looks great, I really appreciate the help.

One thing I've noticed though, the formula for the conditional formatting on the Gantt chart seems to be acting strangely.

It seems to work for most times but if theres a start time of 08:00, it seems to be out by one column.

E.g. if I select a start time of 05/05/2019 08:00, this references period 57 (with either the vlookup or the better formula you gave me). However when the gantt chart populates, it seems to work for the other times though, and if I manually enter 57 in the start ref cell, the gantt populates correctly. I really don't understand the problem with it.

Hope this makes sense.

Thanks

UKFoolsGold

 

[AlanY]

again, it's rounding off problems, if you amend the formula to

=ROUND((C4-DATE(2019,5,3))*24+1,0)
=ROUND((D4-DATE(2019,5,3))*24,0)

that should work