CODEWITHSUNDEEP
exceltimenumberscorresponding-records
user3299009
user3299009

Reputation: 29

How do I convert a row of time to a specific number set?

I have a column of time that begins at 8:30 AM = 90 and steps up to 3:00 PM = 480. I need to convert the times I get to correspond with the correct number. Usually I will just drag the numbers so they match and then copy out the section that need. Now the numbers I get are missing some so dragging ends up skipping sets of numbers. Like I will get 100 different times that need to correspond with the 90 - 480 format (I hope this is a good explanation). Is there a macro or formula that I could use that would convert that would auto-populate the correct number with whatever time I enter? A couple of sections below:

8:30:00 AM  90
8:31:00 AM  91
8:32:00 AM  92
8:33:00 AM  93
8:34:00 AM  94
8:35:00 AM  95
8:36:00 AM  96
8:37:00 AM  97
8:38:00 AM  98
8:39:00 AM  99
8:40:00 AM  100


2:12:00 PM  432
2:13:00 PM  433
2:14:00 PM  434
2:15:00 PM  435
2:16:00 PM  436
2:17:00 PM  437

I want to enter a column of times and automatically get the corresponding number based on the above.

Edit: I think this is less of an time thing and more of a reference thing. I have been looking more and more at this and it seems like vlookup or index formula might get it but I am just not sure.

Edit2: Ok, i am really close now. Basically what I have is =VLOOKUP(A1,D1:E391,2)

I then have this formula pasted into B1 so it retrieves the values; also, I dragged this down so the A1 continues on to A2, A3 and so forth about ~3000 rows. Columns D and E have the table referenced in the formula. I can now paste the time into the A column and the B column will give me the number values. Currently I am getting most of the values I need. The only issue I am having now is some of the results on column B are off.

Example:

9:55 175 8:57 117 9:56 175 8:58 118

Notice there are 2 175s? The 2nd one should be 176. Here are the formulas:

=VLOOKUP(A28,D1:E391,2) =VLOOKUP(A29,D1:E391,2)

Based on that I would think this should give me the 176. This is just one example. I see this sprinkled throughout. Any thoughts on what might cause this?

Thanks a ton! Happy Holidays!

Upvotes: 0

Views: 47

Answers (1)

Marc
Marc

Reputation: 11633

You're just trying to calculate the minutes elapsed since 7:00 AM, right?

It looks to me like you just want the result of this formula:

=(HOUR(A1)-7)*60+MINUTE(A1)

Where cell A1 holds the time you're interested in.

Upvotes: 2

Related Questions