VLOOKUP and truncating time
 

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


--
tvtime
------------------------------------------------------------------------
tvtime's Profile: http://www.excelforum.com/member.php...o&userid=36676
View this thread: http://www.excelforum.com/showthread...hreadid=564159

VLOOKUP and truncating time
 

If you want to truncate to the nearest minute you can use

=INDEX(D3:D30,MATCH(TEXT(A1,"hh:mm"),TEXT(C3:C30," hh:mm"),0))

entered with ctrl + shift & enter


where A1 holds the lookup time value, C3:C30 is the range with times (the
one with the seconds) you lookup the value in and D3:D30 is the range you
return the value from


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com


"tvtime" wrote in
message ...

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


--
tvtime
------------------------------------------------------------------------
tvtime's Profile:
http://www.excelforum.com/member.php...o&userid=36676
View this thread: http://www.excelforum.com/showthread...hreadid=564159

VLOOKUP and truncating time
 

thanks very much for the reply. I'll try it tomorrow!

Have a great day.


--
tvtime
------------------------------------------------------------------------
tvtime's Profile: http://www.excelforum.com/member.php...o&userid=36676
View this thread: http://www.excelforum.com/showthread...hreadid=564159