VLOOKUP with time - Slightly complicated

**deepindreamz** · 03-14-2015, 01:02 AM

Hi,

I am currently designing an excel sheet to help calculate salary and am facing problem with the late coming calculations.

Basically, my sheet 1 has emp name (column A), in time (Column B) and out time (Col C) and a column D where late coming is calculated by subtracting in time from shift start time and is in hh:mm format..

In sheet 2,I have done a vlookup of this data to sheet 2 (Column B) against employee name (column A) and this time again is in hh:mm format..

My scale for late coming deduction is:
Delay Deduction
00:05:00 00:30:00
00:15:00 01:00:00
00:30:00 01:30:00
00:45:00 02:00:00
01:00:00 04:00:00

When I do a vlookup in sheet 2 as Vlookup (b1,Scale array,2,0), I get #N/A

All times everywhere is in hh:mm format..

I have a feeling it is due to Sheet 2 column B has a vlookup formula from sheet A and hence is being considered by excel as a formula

Pls help!!

**FDibbins** · 03-14-2015, 01:25 AM

Hi, welcome to the forum

I have a feeling it is due to Sheet 2 column B has a vlookup formula from sheet A and hence is being considered by excel as a formula

Not so. Excel works (generally) with what is displayed in the cell, not what is behind that (formula etc), so you can have whatever formula you like in a cell (=10+5+5), if it displays, say, 20, then that is what excel works with, not the underlying formula.

You need to understand this about dates (I know, you asked about times, but I feel this is relevant)...a date is just a number indicating how many days have passed since 1/1/1900, so today 7/11/14 is actually 41831. excel formats it into something that we recognise as a date

Likewise with time...time is a decimal of 1 (day), so for instance 06:00 AM is actually just 0.25, 12 noon is .05 and 06:00 PM (18:00) is 0.75. Again, excel formats it to something we see as a time.

If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.