Working with time

[guamlenahans]

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

 

[daniels012]

Vlookup is like this:

=Vlookup(My time,In a list of times 12:00 am to 12:00 pm, Go over 7 rows) The answer will be put in P10.

So if Put this in Cell P10 on Sheet 2:

=vlookup(W7,A1:G24,7)

This looks up the value in W7 '7:32 am in your example
in a list of each hour. This would go down to the 8th row because that is where I have 7:00 am.
Then my code goes over 7 cells to the right from 7:00 am because this is where i have the temperature.

This work??

Michael

 

[guamlenahans]

I got it to work, thanks for you help. One more question, we normally work in 2 waves of support, AM takeoff's and PM takeoffs. With that, we do 2 temperatur sheets, AM window and PM window. Can I write the formual to use a certain one based on the takeoff time, i.e, if the takeoff time is between 0600 and 1800, use the data from the AM sheet, and if between 1800-0600 use the data from the pm sheet?

Thanls a bunch, this will save my forecasters many hours of work.

 

[daniels012]

Sure what column has the AM times and what column has the PM times

 

[barry houdini]

If you just want the lookup range to differ based on the time in W7 then you could use a formula like this, adjust sheet refs and ranges as necessary

=VLOOKUP(W7,IF(AND(W7>="6:00"+0,W7<"18:00"+0),'AM sheet'!B39:V50,'PM sheet'!B39:V50),7)

 

[guamlenahans]

The AM and PM times will be 2 different sheets. Barry, I tried your formula with no luck. I changed the sheet references. My formula was:

=VLOOKUP(W7,IF(AND(W7>="14:00"+0,W7<"24:00"+0),am!BX39:BY50,PM!BX39:BY50),2)

 

[guamlenahans]

I got it, thanks for the help!