Working with time

guamlenahans

Board Regular
Joined
Oct 25, 2006
Messages
113
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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.
 
Upvote 0
I did a test with this formula:
=VLOOKUP(G7,D7:E25,2)

G7 has the time 7:32 a

The list of times I had D7:E25 (not all 24, just adjust to add all times)

Michael
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
They must be entered as times.
type this exactly:
6:00 a
and this will put in 06:00:00 AM

Michael
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

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