Formula for Percentage based on cell reference with times

[Sunshine8790]

Hello all you really smart people
I'm hoping this question falls on the simple side, I'm unable to figure it out.

So, in the example table below you'll see I have a formula showing the current time.
Below that I have a chart with specific times and percentages and a green cell in F4.

My question is: What formula can I place in F4 that will automatically grab the referenced percentages based on the current time?
(For example, if the current time is 12:39 PM, the green cell should display 58.3%, but once the current time is 1 PM the green cell should change to 68.9%)

Book1
	A	B	C	D	E	F	G
1
2
3		12:39 PM
4
5
6
7		7a	9.30%
8		8a	21.00%
9		9a	29.90%
10		10a	37.70%
11		11a	49.40%
12		12p	58.30%
13		1p	68.90%
14		2p	79.50%
15		3p	89.10%
16		4:30p	100.00%
17
18
Sheet1

Cell Formulas
Range	Formula
B3	B3	=NOW()

 

[jasonb75]

With proper times in B7:B16 you could use

=VLOOKUP(MOD(B3,1),B7:C16,2)

I would suggest having an entry in the table for 12AM as well so that you don't get errors before 7AM.

 

[ClaireS]

Are the values in column B actual times, formatted to display in this way? (Custom format ha/p). If they are text values, there would be a lot of work converting these to times within your formula. As long as they are times, then you can use a simple lookup formula: =XLOOKUP(B3,B7:B16,C7:C16,,-1) or =VLOOKUP(B3,B7:C16,2)

 

[Sunshine8790]

With proper times in B7:B16 you could use

=VLOOKUP(MOD(B3,1),B7:C16,2)

I would suggest having an entry in the table for 12AM as well so that you don't get errors before 7AM.

Perfect! Thank you so much, and I have updated the entry to include 12 AM.

 

[jasonb75]

As long as they are times, then you can use a simple lookup formula:

You would need to use the MOD function with it as I have done in order to separate the date and time returned by NOW() otherwise the lookup is always going to return the last %age in the table.

 

[ClaireS]

My mistake: I didn't take on board that this was a NOW() function. I shall read the questions more carefully in future! I was more concerned that the very abbreviated time column was actual times rather than text values.

 

[jasonb75]

I was more concerned that the very abbreviated time column was actual times rather than text values.

In case you're not aware, in the mini sheet they are text (if you hover over formatted cells in the mini sheets you can see the underlying values, if nothing shows then they are text), although from the OP's response I would assume that the actual sheet contains proper times.

I shall read the questions more carefully in future!

I've lost count of how many times I've said that and forgotten to do it with the next question