how to use Now() function in hh:mm format for vlookup??

**naira** · 04-13-2012, 05:05 PM

hi!

I need help in using Now() function in hh:mm format for doing a vlookup.

I am getting the current time using NOW() Function and my previous day's data is referenced with time in hh:mm format (not just in display format, but even created at backend in hh:mm format by actually typing in). But when I do a vlookup, I am not getting the data in the output since excel calculates the time in backend in decimal places, not just in hh:mm format for vlookup.

For example, let us say the current time using Now() function is 09:15 (am) which is something like 41012.3854195602 (for 13 April 2012) for excel's own internal calculation. In another sheet I have entered data for 09:15 (am) which is equivalent to 0.385416666 for excel's calculation purpose.

To match the decimals of Now() data for vlookup with with manual entry time, I took the Now() time and rounded it off to 4 decimal places so that it was matching the decimals of manual entry time. Therefore, 0.38541 of Now() is now equal to 0.38541 of Manual entry.
So when I do a vlookup (and while my data is being updated in my excel sheet in real time for Now() function to work), I will get the vlookup result.
However, sometimes when the rounded off values of Now() are not equal to Rounded off values of manual entry time, there is a mismatch and the data jumps to the last entry for 1-2 seconds, before again reverting back to actual current time.

I was wondering if there is a more elegant solution to match the Now() time in hh:mm with my manually entered time in hh:mm. Please note that I am not talking about display in hh:mm format by changing the time format, but for actual time value in hh:mm format as read by excel.
What I am unable to do is not being able to use is the FALSE condition with my vlookup during comparison of time. Since excel calulates time in decimals and not in human time format, I am currently forced to work with only for approximate matches which is giving me problems.

Following 2 points may please be noted in this regard:
1. Even though I am talking in terms of vlookup about, my data is in array format and I am fetching it using Index and Match functions, which are basically same as vlookup;
2. I would prefer a formula running into multiple columns and rows rather than a Macro since macros generally do not allow reverting of calculations once done. But even than, visitors may advise me on Macros.

Regards,

Naira.

**icestationzbra** · 04-13-2012, 05:25 PM

naira:

it may be more helpful if you uploaded a sample file.

**FDibbins** · 04-13-2012, 05:48 PM

when you use now() it includes seconds as well, so it is unlikely to find an exact match with an entry that just hrs and minutes. try something like =HOUR(NOW())&MINUTE(NOW()) for your match?