Ref Reverse Lookup in VBA

[chihuahua]

In video MrExcel & Excelisfun Trick 7, a Function uses dates and times for listing Truck appointments. The dates work fine but the time only works when entered as text. Is that true for all time procedures ?

 

[dermie_72]

See if this helps.
TIME function - Excel

 

[chihuahua]

Thanks for your response but no that doesn't help. When the VBA function in run the time shows up as a decimal (8:00 AM shows up as .3333333),unless the time is entered as text , '8:00 AM. The decimal is not converted to a time like the date serial number is converted to a date. Formatting the cell doesn't change the decimal either.

 

[dermie_72]

can you post an example of the code you're referring to

 

[chihuahua]

this is the table: Results go here:

11/19/08 11/20/08 11/21/08 Truck Appointment
8:00 AM Truck1 Truck4 Truck9
9:00 AM Truck8 Truck5 Truck1 Truck1 11/19/08 8:00 AM; 11/21/08 9:00 AM <-- This is result but when time is entered as a time not as text (')the
10:00 AM Truck9 Truck11 Truck5 Truck2 result is shown as decimal (.33333333 for 8:00 AM and .375
11:00 AM Truck6 Truck4 Truck2 Truck3 for 9:00 AM).. The dates are fine. They calculate perfectly.
Truck4
Truck5
Truck6
Truck7
Truck8
Truck9
Truck10
Truck11
The code:
Function RL(truckvalue as range,lookuptable as range)
hrow = lookuptable.rows(1).row-1
hcol = lookuptable.columns(1).column-1
RL = ""
for each cell in lookuptable
if cell.value=truckvalue.value then
RL = RL & cells(hrow,cell.column).value & " " & cells(cell.column,hcol).value & ";"
end if
next cell
End function

All of this in straight from Dueling Excel with MrExcel & Excelisfun episode Reverse Lookup (trick #7)

sorry this didn't turn out.. Wont let me paste it from excel (2010)

 

[chihuahua]

https://www.youtube.com/watch?v=qD6hO53cRP4 I did find a way to put the link in here: