# Working with time

#### guamlenahans

##### Board Regular
I have a spreadsheet that users input the temperature each hour, for a 24 hour period. The time used is 24hr clock, as example, cell A1 will be 03:00, and cell B1 would be the forecasted temperature.

On another sheet, the user inputs the temperature for the specific time a airplane is going to take off, i:e: 07:32. Is there a way I can have the second sheet look back to the first sheet and grab the temperature for the correct time (using the hour block it falls into). The first sheet time blocks are every whole hour, i.e 08:00, but on the second sheet, the time could be any hour/minute, 07:32.

Thanks Rob

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Datsmart

##### Well-known Member
This formula will gather the temperature from a table that has your times and Temps.
Code:
``=VLOOKUP(A1,Sheet2!A1:B24,2)``
It assumes your Time is in column A and Temperatures are in column B of Sheet2.
Cell A1 of the active sheet contains the "Take Off" time.

#### daniels012

##### Well-known Member
I did a test with this formula:
=VLOOKUP(G7,D7:E25,2)

G7 has the time 7:32 a

Michael

#### guamlenahans

##### Board Regular
I need to look in cell W7 of sheet 2, which has the takeoff time, then cross reference back to sheet 1 to fine the corresponding temperature forecast for that time, and then put the temp for the forecast in cell P10 of sheet 2.

Hope I'm not confusing anyone...

Thanks

Rob

#### Datsmart

##### Well-known Member
Slightly adjusted formula from my first post to match your latest criteria.
Code:
``=VLOOKUP(Sheet2!W7,Sheet1!A1:B24,2)``
It assumes your Time is in column A and Temperatures are in column B of Sheet1.
Take Off time is in cell W7 of sheet2.

#### guamlenahans

##### Board Regular
I keep getting 0 as the formual result. On the temp input sheet, the time blocks are labeled as only one hour, ie:
A1: 06:00
A2: 07:00, etc.

Do I need to list them as:

A1: 06:00-07:00...?

Thanks

Rob

#### daniels012

##### Well-known Member
They must be entered as times.
type this exactly:
6:00 a
and this will put in 06:00:00 AM

Michael

#### indiantrix

##### Well-known Member
The likely cause is that your times are text items and not actual numeric times. Make sure your numbers are not text by testing with =ISTEXT(A1) and correct as needed, then your formula should return valid results. NOTE: This check should be performed on BOTH columns of "times". HTH Larry

#### guamlenahans

##### Board Regular
OK, some progress. I can get it to work for only the first 2 coluns of the vlookup. The temp is in column 7, not 2. I have changed the formula but I still get the 0 as the result. I have tried to format the cell for number, but no luck. Any thoughts?

This is what I chaged the formual to so you can see what cells I'm using:

=VLOOKUP(W7,'LOCAL (2)'!B40:V50,1,2)

#### guamlenahans

##### Board Regular
=VLOOKUP(W7,'LOCAL (2)'!B39:V50,7,TRUE)

This is the actual formaula I used, I put the wrong one in before.

Replies
7
Views
343
Replies
3
Views
494
Replies
15
Views
409
Replies
8
Views
723
Replies
0
Views
290

1,181,102
Messages
5,928,072
Members
436,586
Latest member
latintxn

### 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.

### Which adblocker are you using?

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

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