help with time formula please

orsm6

Active Member
Joined
Oct 3, 2012
Messages
496
Office Version
  1. 365
Platform
  1. Windows
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
SMC Schedule - revamp.xlsm
A
75Thursday 00:00
76Thursday 01:00
77Thursday 02:00
78Thursday 03:00
79Thursday 04:00
80Thursday 05:00
81Thursday 06:00
82Thursday 07:00
83Thursday 08:00
84Thursday 09:00
85Thursday 10:00
86Thursday 11:00
87Thursday 12:00
88Thursday 13:00
89Thursday 14:00
90Thursday 15:00
91Thursday 16:00
92Thursday 17:00
93Thursday 18:00
94Thursday 19:00
95Thursday 20:00
Input


the cell to return value
SMC Schedule - revamp.xlsm
E
5#N/A
SMC Planner
Cell Formulas
RangeFormula
E5E5=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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Today is Wednesday the 8th. Is there date-time values in column A for Wednesday the 8th
 
Upvote 0
Don't worry -- we all have the same moments once in a while. I had mine just a few days ago.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

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