If statement to give a 'yes' or 'no' if a time fall between a range

[chrisb360]

Hi all,

I am trying to get a yes/no answer if a cell is between a time range. It is set up as a table on Office 365. I have tried IF and IFS statements with both AND and TIMEVALUE in it. I have attached a screen grab of what i'm working with at the moment.

Any help is greatly appreciated.

Chris


View attachment 46775

 

[chrisb360]

 

[Peter_SSs]

Welcome to the Mr Excel board!

We cannot tell much from an image. MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[chrisb360]

QF 96 Driver supplement.xlsx
	F	G	H	I	J	K
6	Start Time	Start Time2	End Time	Time on road	Mileage	Early start
35	06/09/2021 06:08	06:08:18	06/09/2021 18:20	12:12:20	324.81	No
36	06/09/2021 07:39	07:39:41	06/09/2021 13:57	06:18:00	194.619	No
37	06/09/2021 08:31	08:31:55	06/09/2021 19:51	11:19:44	266.403	No
Sheet1

Cell Formulas
Range	Formula
I35:I37	I35	=IF([@[Start Time]]<>"",[@[End Time]]-[@[Start Time]],"")
K35	K35	=IFS([@[Start Time]]>=TIMEVALUE("06:30:00"), "No",[@[Start Time]]>=TIMEVALUE("00:00:00"),"Yes")
K36	K36	=IF([@[Start Time]]>=TIMEVALUE("06:30:00"), "No",IF([@[Start Time]]>=TIMEVALUE("06:30:00"),"Yes"))
K37	K37	=IF(AND([@[Start Time]]<TIMEVALUE("06:30:00"),[@[Start Time]]>TIMEVALUE("00:00:00")),"Yes","No")

 

[Dave Patton]

consider

Time.xlsm
	F	G	H	I	J	K
35	06-Sep-21 06:08	06-Sep-21 06:08	06-Sep-21 18:20	12:12	324.81	No
36	06-Sep-21 07:39	06-Sep-21 07:39	06-Sep-21 13:57	06:18	194.619	Yes
3e

Cell Formulas
Range	Formula
I35:I36	I35	=IF(F35<>"",MOD(H35,1)-MOD(F35,1),"")
K35:K36	K35	=IF(MOD(F35,1)>=TIMEVALUE("06:30:00"), "Yes","No")

between a range of times

Time.xlsm
	F	G	H	I	J	K
38	06-Sep-21 06:08	06-Sep-21 06:08	06-Sep-21 18:20	12:12	324.81	Yes
3e

Cell Formulas
Range	Formula
I38	I38	=IF(F38<>"",MOD(H38,1)-MOD(F38,1),"")
K38	K38	=IF(AND(MOD(F38,1)>=TIMEVALUE("00:00:02"),MOD(F38,1)<=TIMEVALUE("06:30:00")), "Yes","No")

 

[chrisb360]

consider

Time.xlsm
	F	G	H	I	J	K
35	06-Sep-21 06:08	06-Sep-21 06:08	06-Sep-21 18:20	12:12	324.81	No
36	06-Sep-21 07:39	06-Sep-21 07:39	06-Sep-21 13:57	06:18	194.619	Yes
3e

Cell Formulas
Range	Formula
I35:I36	I35	=IF(F35<>"",MOD(H35,1)-MOD(F35,1),"")
K35:K36	K35	=IF(MOD(F35,1)>=TIMEVALUE("06:30:00"), "Yes","No")

between a range of times

Time.xlsm
	F	G	H	I	J	K
38	06-Sep-21 06:08	06-Sep-21 06:08	06-Sep-21 18:20	12:12	324.81	Yes
3e

Cell Formulas
Range	Formula
I38	I38	=IF(F38<>"",MOD(H38,1)-MOD(F38,1),"")
K38	K38	=IF(AND(MOD(F38,1)>=TIMEVALUE("00:00:02"),MOD(F38,1)<=TIMEVALUE("06:30:00")), "Yes","No")

Hi Dave,

its not so much between the two numbers in the cells but if the start time in F35 is between 00:00:00 and 06:30:00 then I need it to say "yes" and if not then "no",

I also tried importing what you put and I get 'FALSE' error

Chris

 

[chrisb360]

Hi Dave,

I change the greater than sign in K35 and that worked perfectly. Thank you so much for your help

Chris

consider

Time.xlsm
	F	G	H	I	J	K
35	06-Sep-21 06:08	06-Sep-21 06:08	06-Sep-21 18:20	12:12	324.81	No
36	06-Sep-21 07:39	06-Sep-21 07:39	06-Sep-21 13:57	06:18	194.619	Yes
3e

Cell Formulas
Range	Formula
I35:I36	I35	=IF(F35<>"",MOD(H35,1)-MOD(F35,1),"")
K35:K36	K35	=IF(MOD(F35,1)>=TIMEVALUE("06:30:00"), "Yes","No")

between a range of times

Time.xlsm
	F	G	H	I	J	K
38	06-Sep-21 06:08	06-Sep-21 06:08	06-Sep-21 18:20	12:12	324.81	Yes
3e

Cell Formulas
Range	Formula
I38	I38	=IF(F38<>"",MOD(H38,1)-MOD(F38,1),"")
K38	K38	=IF(AND(MOD(F38,1)>=TIMEVALUE("00:00:02"),MOD(F38,1)<=TIMEVALUE("06:30:00")), "Yes","No")

 

[Dave Patton]

Thanks for the feedback. Dave