help with time formula please

orsm6

Active Member
Joined
Oct 3, 2012
Messages
357
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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
Today is Wednesday the 8th. Is there date-time values in column A for Wednesday the 8th
 

orsm6

Active Member
Joined
Oct 3, 2012
Messages
357
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Maybe

=VLOOKUP(NOW(),Input!A:A,1,1)
i was sure i tried this and it didn't work.

seems to work now though....
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,639
Don't worry -- we all have the same moments once in a while. I had mine just a few days ago.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,380
Messages
5,601,293
Members
414,440
Latest member
Kim0204

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