Time values got me stumped

swaink

Active Member
Joined
Feb 15, 2002
Messages
432
Hi All

I wonder if I may ask for some help please.

I am working a very complex piece at the minute and am struggle with one area.

In column H5 down I have time values(HH:MM), in row I4:BO4 I again have time values(HH:MM)

My code looks for the time in Column H and finds it in row 4, all works well until I get to times after 22:00, where the code then jumps to the last column every time (05:30)

I have noticed that whilst the cells are formated as time(HH:MM) when i see the values in the code they appear as decimal values for example 0.9375

The code steps through each time in column H to match the time in the row and looks like this

mycolumn = Application.Match(ActiveCell.Value, Range("A4:BO4"), 1)

I've tried to DIM mycolumn as Integer and Double but no success

I would really appreciate some advise on this one

Regards

Kevin
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Try:

Rich (BB code):
mycolumn = Application.Match(ActiveCell.Value, Range("A4:BO4"), 0)

Your data isn't sorted ascending so you need an exact match.
 
Upvote 0
Hi Andrew

Thank you for your time.

I did try that but it then fell over when it tried to match 17:00:00, I reformated the cells just in case but no joy.

The times in the row starts at 15:00:00 through to 05:30 the following morning

Kev
 
Upvote 0
Andrew

My appologies

Allthough every thing looked ok, I have re keyed in all of the time values in both the row and the column and it does now work

Thank you
 
Upvote 0
Did you use AutoFill to enter the list of times? That seems to cause a precision issue. Does it work if you press F2 Enter on each cell in the list?
 
Upvote 0
Hi Andrew

I'm was not sure how the original data was entered, so thought I'd better re key just to be sure.

Can I ask a further question though, the time values in the row are in quarter hour segments, is it possible to find the next nearest match for example if a user entered an odd time such as 19:25 it would find the 19:30 segment

Kev
 
Upvote 0
To do that your times would have to be in ascending order. You can add 1 to the times that are on the next day. Of course you will also need to add 1 to the lookup time if it's on the next day.
 
Upvote 0

Forum statistics

Threads
1,224,584
Messages
6,179,693
Members
452,938
Latest member
babeneker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top