I have a spreadsheet for my work. I want to know how or what formula to use to get the Height of Tide (HOT) base on the Eta of the ship. The Eta cell is formatted to DD Mmm YYYY @ HH:mmLT? Eg:-
EXTRACT FROM TIDE TABLE
DEC 2022
Date/Time 17 18 19 20 21 22 23
30 2.1 2.3 2.4 2.7 2.8 2.8 2.6
31 2.3 2.2 2.0 1.8 1.7 1.9 2.1
JAN. 2023
Date/Time 00 01 02 03 04 05 06
01 2.0 2.3 2.4 2.7 2.8 2.8 2.6
02 2.3 2.2 2.0 1.8 1.7 1.9 2.1
Base on Eta of ship: 31st Dec 2022 @ 2300LT (Ans: 2.1)
1)How can use formula to get the exact HOT base on the Eta?
2)HOT 1hr before the Eta (31st Dec 2022 @ 2200LT) – (Ans:1.9)
3)HOT 1hr after the Eta (01st Jan 2023 @ 0000LT) – (Ans:2.0)
By using formula, I don’t have to refer to the hard copy or soft copy anymore. With formula the answer will be extract automatically. Moreover some of my colleague are not computer savvy. Hence they prefer just to have a single click & all the information required are there.
It will also reduce time & error while looking over the tables for the HOT.
—–
SID
So is the idea you want to look up a number based on two dimensions: date and hour? If so, you've first got to create a proper database table. The one you have looks good, but to be used by functions to look things up it needs to be more straightforward.
Each row should represent a date. The only row without data for a date should be the header row with the column titles. The first column of each row should be the date, and it should be a header column. There then should be 24 more columns each with the header row being 0 through 23 for each hour.
Now you can use two MATCH functions to find the right column and row given any date and hour, and the INDEX function to get the value from the cell at that intersection.
So if your data is in Table 1, then this formula in Table 2 will get you the value at 1/6/2023 for hour 7.
INDEX(Table 1::A$1:E$10,MATCH(DATE(2023,1,6),Table 1::A),MATCH(7,Table 1::$1:$1))
Of course the values DATE(2023,1,6) and 7 can instead be references to other cells in that second table. So you just change the date and hour cells and the result shown by the formula changes.
See https://macmost.com/using-index-and-match-functions-to-look-up-values-in-numbers.html
INDEX(Table 1::A$1:E$10,MATCH(DATE(2023,1,6),Table 1::A),MATCH(7,Table 1::$1:$1))
The formula above only match for year 2023. Cos if the Eta 31st Dec2022 @2300LT, the next hour will be 01st Jan 2023. So the cell with formula index match should recognise whether to find from the data sheet Dec 2022 or Jan 2023. Can’t we index match with separate index as if the date Dec2022 to find on the index Dec 2022, if Jan 2023 to find on index Jan 2023.
SID: The date column should have the complete date in it, month, day and year. It should be a regular date value. Then the DATE(2023,1,6) value in the formula should be a date as well.
Noted on date column. How about the time column? I’m trying to extract or refer to the ETA cell in following format : DD Mmm YYYY @ HH:mmLT.
But how do i extract the time from above??
Understand Date in the formula : DATE(2023,1,6)
SID: I was suggesting that the time column just be the hour: 0, 1, 2, 3, 4, etc.
Understand date column contain: Year, month & date. Time Column just be in hour.
Formula given: INDEX(Table 1::A$1:E$10,MATCH(DATE(2023,1,6),Table 1::A),MATCH(7,Table 1::$1:$1))
Eg: ETA CELL: 31st Dec 2022 @ 23:00LT
I require the formula to extract the year, month, date & time from the ETA CELL
Date should ref the ETA cell & pick up: 31 Dec 2022
Time should reference the ETA cell & pick up the time 23:00
Otherwise I have to keep changing this (DATE(2022,12,31) , (DATE(2023,01,01), etc
SID: Yes, do exactly that. Replace DATE(2023,1,6) with a reference to a cell that has a date in it. Like Table 2::$A$2 for instance, if A2 in Table 2 has a date. But there should be no time in that cell. It should just be a date. So then maybe Table 2:$A$3 should be the hour. Then change the 7 in the formula to a reference to that cell.
The problem is that the reference cell is formatted such that it has the date & time.
Exactly like this the REF CELL: 31st Dec 2022 @ 23:00LT.
Is there anyway I can separate the date & the time? Then it would be perfect.
Sid: Yes. Use the date and time functions for that. For instance, HOUR gets the hour, etc.
Unfortunately it doesn’t work that way. I already use the date & time function & convert to 23 or 2300. Even though it shows Hour only but I reckon at the background it still contains the date.
Whenever I try to match with the TIME column in Table 1 & error message appear. (Match could not find 23 or 2300)
The format is different. Guess need to extract the totally ‘TIME ONLY’ from the ETA REF CELL without the date.
Is there any way to do that??
Sid: Right. As I mentioned in a previous comment, use HOUR to get the hour from a date. So replace the 7 in the formula with HOUR(reference to a cell with the date and time).
Hi Gary, thank you so much. I finally manage as I would like it t be. But I can’t use the ‘DATE’ function as per your formula. Cos error message that ‘DATE’ require 3 component (YYYY, MMM,DD) I just ‘MATCH’ only to the Ref Cell.
It works perfectly : IFERROR(INDEX(Table 2::A2:Z96,MATCH(REF CELL)::F2,Table 2::A2:A96),MATCH(HOUR(REF CELL),Table 2::2:2)),"~")
I have another formula required which is similar. I guess I start with a new question.
Thanks Gary once again.