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

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
Joined
Jun 19, 2003
Messages
7,985
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
Joined
Jan 13, 2005
Messages
5,219
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
 

guamlenahans

Board Regular
Joined
Oct 25, 2006
Messages
113
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
Joined
Jun 19, 2003
Messages
7,985
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
Joined
Oct 25, 2006
Messages
113
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
Joined
Jan 13, 2005
Messages
5,219
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
Joined
Oct 17, 2002
Messages
943
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
Joined
Oct 25, 2006
Messages
113
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
Joined
Oct 25, 2006
Messages
113
=VLOOKUP(W7,'LOCAL (2)'!B39:V50,7,TRUE)

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

Forum statistics

Threads
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.
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
Top