Formula to determine if a date and time are within a range.

[patrick_oneal]

Hello,

Completely stumped here. Thought I would take a change and add some experts.

I need to be able to reference a table that is attached as an image. I need a formula that can read a cell that says "44027 11:00" find where it is on the reference table and return the code associated with the day. The difficult thing is that the 44027 11:00 is the result of other formulas so I don't know if that is why it is throwing my search results.

 

[GWteB]

The difficult thing is that the 44027 11:00 is the result of other formulas so I don't know if that is why it is throwing my search results.

If "44027 11:00" is in one cell, your formulas probably aren't set up in a way they supposed to. The number 44027 is (as a date) equal to July 15, 2020. If the intention is to populate that particular cell with "July 15, 2020 11:00" the outcome should be (as a number) 44027.4583333333
The formatting of a cell determines if Excel treats a number as a number or as a date & time.

 

[DRSteele]

Your date 44027 11:00 is invalid. Whatever formula is reporting it needs to be adjusted to report a decimal number. 44027 11:00 should be 43845.45833

Try this:

MrExcel posts18.xlsx
	A	B	C	D	E	F
1	start	end	code		lookup date	code
2	1/1/20 10:00 AM	1/2/20 9:59 AM	a1		44027 11:00	#N/A
3	1/2/20 10:00 AM	1/3/20 9:59 AM	a2		1/15/20 11:00 AM	a15
4	1/3/20 10:00 AM	1/4/20 9:59 AM	a3		43845.45833	a15
5	1/4/20 10:00 AM	1/5/20 9:59 AM	a4		7/15/20 11:00 AM	a20
6	1/5/20 10:00 AM	1/6/20 9:59 AM	a5		44027.45833	a20
7	1/6/20 10:00 AM	1/7/20 9:59 AM	a6
8	1/7/20 10:00 AM	1/8/20 9:59 AM	a7
9	1/8/20 10:00 AM	1/9/20 9:59 AM	a8
10	1/9/20 10:00 AM	1/10/20 9:59 AM	a9
11	1/10/20 10:00 AM	1/11/20 9:59 AM	a10
12	1/11/20 10:00 AM	1/12/20 9:59 AM	a11
13	1/12/20 10:00 AM	1/13/20 9:59 AM	a12
14	1/13/20 10:00 AM	1/14/20 9:59 AM	a13
15	1/14/20 10:00 AM	1/15/20 9:59 AM	a14
16	1/15/20 10:00 AM	1/16/20 9:59 AM	a15
17	1/16/20 10:00 AM	1/17/20 9:59 AM	a16
18	1/17/20 10:00 AM	1/18/20 9:59 AM	a17
19	1/18/20 10:00 AM	1/19/20 9:59 AM	a18
20	1/19/20 10:00 AM	1/20/20 9:59 AM	a19
21	1/20/20 10:00 AM	1/21/20 9:59 AM	a20
Sheet37

Cell Formulas
Range	Formula
E4,E6	E4	=E3
F2	F2	=VLOOKUP(E2,A2:C21,3)
F3	F3	=VLOOKUP(E3,A2:C21,3)
F4	F4	=VLOOKUP(E4,A2:C21,3)
F5	F5	=VLOOKUP(E5,A2:C21,3)
F6	F6	=VLOOKUP(E6,A2:C21,3)