YouTubersLoveExcel#48: VLOOKUP for TIME

I am trying to create a timesheet spreadsheet, where it calculates the time worked, looks this value up and produces the time after breaks.

The formula i am using is =(VLOOKUP((Timesheets!L5-Timesheets!K5)+(Timesheets!K5>Timesheets!L5),$D$4:$F$99,3,1))

K5 = Start Time
L5 = Finish Time
$D$4:$F$99 = Lookup Table

It seems to work ok however using the below table the answer seems to mess up when it overlaps the 4:00, 8:00 marks, (This is when the breaks increase).

It produces a figure that is 15 minutes less. Is there a reason for this or would you suggest using a different formula??

Hours Worked (HH:MM Format) Decimal Format Hours Worked After Breaks (HH:MM Format) 0:00 0.00 0:00 0:15 0.25 0:15 0:30 0.50 0:30 0:45 0.75 0:45 1:00 1.00 1:00 1:15 1.25 1:15 1:30 1.50 1:30 1:45 1.75 1:45 2:00 2.00 2:00 2:15 2.25 2:15 2:30 2.50 2:30 2:45 2.75 2:45 3:00 3.00 3:00 3:15 3.25 3:15 3:30 3.50 3:30 3:45 3.75 3:45 4:00 4.00 4:00 4:15 4.25 4:00 4:30 4.50 4:15 4:45 4.75 4:30 5:00 5.00 4:45 5:15 5.25 5:00 5:30 5.50 5:15 5:45 5.75 5:30 6:00 6.00 5:45 6:15 6.25 6:00 6:30 6.50 6:15 6:45 6.75 6:30 7:00 7.00 6:45 7:15 7.25 7:00 7:30 7.50 7:15 7:45 7.75 7:30 8:00 8.00 7:45 8:15 8.25 7:45 8:30 8.50 8:00 8:45 8.75 8:15 9:00 9.00 8:30 9:15 9.25 8:45 9:30 9.50 9:00 9:45 9.75 9:15

Hi,

I am new here although I have used it the forum as a guest a few times.

I have a problem and am not sure it is possible but if I am just not good enough.

On sheet 1 I have data in chronological order wit start and end date and time.

On sheet 2 I have other information by date and time. I am now trying to link the Sheet two times to the ranges defined on sheet 1. When using "TRUE", it gives me the wrong bit of information (the one above).

Any help would be much appreciated. I will try and upload a sample once I found out how

Thanks

Christopher

P.S.: I use Excel 2007

Sheet 1:

Start time End Time Data
1/12/09 06:00 1/12/09 06:18 b5648

Sheet 2:

Time Data2 Data???
1/12/09 06:06 790 (B5648)

Hey guys:

Cell A1: A forumla that takes the current time rounds it to the nearest 30 minutes, and then adds 1 hour to convert it from central to eastern time. Also, this formula subtracts TODAY() so I get a raw time value.

Code:

=(ROUND(NOW()*48,0)/48)+TIME(1, 0, 0)-TODAY()

Cell B2: A VLOOKUP or SUMIFS that will look at the value of A1 and find that value in column M, and then return the value in column N.

Code:

=SUMIFS(N:N,M:M, A1)

Column M: Time
Column N: Needed value

My Problem: Doing a vlookup does not work, doing a sumifs only works for certain times. Am I missing something here?

Hi all,

I'm pretty new to VBA, but I've done a lot of searching on the web and I can't find a solution to this. Could I have some help, please?

I have a table with two columns. The first contains text, which I want to look up values in. The second contains numbers (with decimal points - floating point numbers?) which I want to return. VLookup is the function to use, I'm sure. But, I have a problem getting it to work. Here's the code:

Code:

'SEARCH BY DESCRIPTIVE GROUP
Sub SearchByDescriptiveGroup()
Dim descriptivegrouptable As range
Dim descriptivegroup As String
Dim assigningroughnesses As Double
descriptivegroup = "Building"

Set descriptivegrouptable = Application.Worksheets("Roughness Heights").range("A2:B22")
assigningroughnesses = Application.VLookup(descriptivegroup, descriptivegrouptable, 2)

MsgBox assigningroughnesses


End Sub

In theory, this should find the word "Building" in the table and return the number (signifying roughness) in the same row, but in the next column.

However, on running it, I get a "Run-time error '13': Type mismatch". When I debug, it highlights the line containing the VLookup.

I have a feeling that Excel has a problem extracting data from a range and placing it in a Double, but if so, I don't know why.

Can anyone shed some light on this, please? I expect it's something really obvious that I haven't seen because of my inexperience, but I've spent hours trying to solve this one problem!

Cheers for any help,

Liam

Hi,

I am normally quite good with lookups, but I have a question:

In the example file there is data for peaks at two gauging stations, I want to :

a) find the peaks in column I that correspond to the peaks in column D...
if I write =VLOOKUP(B3,$G$3:$I$453,3,FALSE) then I match the peaks that are on the same day..

b) if (say) one of the stations peaked at 11:45pm and the other at 4am the next day I won't get a match, so I would like to insert a IF(or similar). I don't want to use the TRUE switch at the end of the lookup because ideally I'd like to match the individual peaks..

c) in the future there will be up to five stations data I will want to match!

d) I will also be comparing the times of the peaks (I can do this using the formula I gave above but modified to return a different column)

e) Ideally I'd to do this using formulas rather than a macro

f) Is there any way to use a lookup and then return a value from coulmn with the reference -2?

Thanks

Hello to any who can help... and thank you.

I am converting from another application into excel, and the column for time includes the seconds. I would like to be able to truncate the seconds in order to perform a VLOOKUP. I can include every minute of the day in 1440 rows, but seconds is just too many. Merely formatting the cells, or rounding (i've tried converting to integers) doesn't work because excel knows what's there, whether shown or not.

Thanks in advance for any help.
Kiley

Hi All,

I want to Vlookup with website and email addresses. Is it possible to do that at one time? for example

I have a list with 200+ contacts and in another lists there are 50 records. In both the lists there are website and email addresses are available. I am using vlook up for 2 times one time I am going with by websites and the second time I am going for email addresses.

Is it possible to do that in a single time?

Waiting for your response

I am having a problem using VLOOKUP when using a date and time. The Column "
Date TIME" has formatted cells in the form of MM/DD/YY HH:MM. In Column B (#
MSGS) I have a value in this case it is message counts.
I have a second Table consisting of "Date Time" Column also in the fomrat of
MM/DD/YY HH:MM. I want to match the "Date Time" Value in my Second Table
against the "Date Time" Value of my first table and extract the value of "#
Msgs" of this First table and store it in the MSGS column of the second table)

this is the formula I am using
=VLOOKUP(A1,TSAT_28JUL05_Activity!$B$5:$C$706,2,FALSE)

Basically A1 is the cell of my second table "Date Time" in this case and
TSAT_28JUL05_Activity!$B$5:$C$706 are the Date/Time and Msgs of my first
table. What am I doing wrong? I appreciate your help. Thank You!
First Table

Date Time # Msgs
7/21/05 0:03 1
7/21/05 0:05 15
7/21/05 0:06 3
7/21/05 0:07 1
7/21/05 0:10 3
7/21/05 0:11 6
7/21/05 0:15 5
7/21/05 0:16 5
7/21/05 0:20 5



Second Table

Date Time Count
7/21/05 0:01
7/21/05 0:02
7/21/05 0:03
7/21/05 0:04
7/21/05 0:05
7/21/05 0:06
7/21/05 0:07
7/21/05 0:08
7/21/05 0:09
7/21/05 0:10
7/21/05 0:11
7/21/05 0:12

Hi I am new to this forum and would really appreciate it if some one could help me. I am trying to lookup a shop name in a worksheet and derive how many days of the week it made a sell from looking up another workbook. Please note that shops names are not in order so I thought a formula with a countif and vlookup could work, but I cant get it seem to work.
Please see attachment for what I am trying to achieve.
Many thanks in advance for your help.