VLOOKUP and truncating time

[tvtime]

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

 

[Peo Sjoblom]

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

=INDEX(D330,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 D330 is the range you
return the value from


--


Regards,

Peo Sjoblom

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

 

[tvtime]

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

Have a great day