what on earth am i doing wrong here?
i am trying to look up the current day and time but i get #N/A errors.
lookup range: InputA:A
lookup range formatted: dddd hh:mm the cell displays Thursday 08:00 but the value is 09/04/2020 08:00 AM (there seems to be 2 spaces between date and time)
cell to return value formatted: dddd hh:mm
lookup data sample
the cell to return value
the reason i am using floor is that i want to find the start of the current hour in the Input range.... so if it is 08:48 right now, it finds and returns 08:00
i am trying to look up the current day and time but i get #N/A errors.
lookup range: InputA:A
lookup range formatted: dddd hh:mm the cell displays Thursday 08:00 but the value is 09/04/2020 08:00 AM (there seems to be 2 spaces between date and time)
cell to return value formatted: dddd hh:mm
lookup data sample
SMC Schedule - revamp.xlsm | |||
---|---|---|---|
A | |||
75 | Thursday 00:00 | ||
76 | Thursday 01:00 | ||
77 | Thursday 02:00 | ||
78 | Thursday 03:00 | ||
79 | Thursday 04:00 | ||
80 | Thursday 05:00 | ||
81 | Thursday 06:00 | ||
82 | Thursday 07:00 | ||
83 | Thursday 08:00 | ||
84 | Thursday 09:00 | ||
85 | Thursday 10:00 | ||
86 | Thursday 11:00 | ||
87 | Thursday 12:00 | ||
88 | Thursday 13:00 | ||
89 | Thursday 14:00 | ||
90 | Thursday 15:00 | ||
91 | Thursday 16:00 | ||
92 | Thursday 17:00 | ||
93 | Thursday 18:00 | ||
94 | Thursday 19:00 | ||
95 | Thursday 20:00 | ||
Input |
the cell to return value
SMC Schedule - revamp.xlsm | |||
---|---|---|---|
E | |||
5 | #N/A | ||
SMC Planner |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E5 | E5 | =VLOOKUP(FLOOR(NOW(),1/24),Input!A:A,1,0) |
the reason i am using floor is that i want to find the start of the current hour in the Input range.... so if it is 08:48 right now, it finds and returns 08:00