Greater than equal to Less than with Days and Hours equalling a certain number

[exc]

Hello, I'm trying to create a formula to automatically calculate a number based on the amount of time (Days and Hours).

Here's the values I need to input.

If C2 is greater than or equal to 4 Hours but less than 8 hours =1, if C2 is greater than or equal to 8 hours but less than 12 hours =2, if C2 is greater than or equal to 12 hours but less than 1 days and 4 hours =3. If C2 is greater than or equal to 1 days 4 hours but less than 1 days 8 hours =4, if C2 is greater than or equal to 1 days 8 hours but less than 1 days 12 hours =5.

Hopefully this isn't too complex to write, thanks in advance!

 

[jtakw]

Hi,

What exactly is in C2, a Date/Time value, or just numbers?
Can you post a sample?

 

[exc]

Sure, one cell says 18 hours, 52 minutes. Another one says 1 days, 8 hours, 30 minutes. The minutes don't really matter to me just days and hours.

 

[jtakw]

Is it Real Date/Time values (a valid number) custom formatted to read as x days x hours, x minutes, or Text?
What does =ISNUMBER(C2) return?

 

[exc]

=ISNUMBER(C2) returns false. Here is the formula I used to create the C2 value. =IF(INT(B2-A2)>0,INT(B2-A2)&" days, ","")&IF(HOUR(B2-A2)>0,HOUR(B2-A2)&" hours, ","")&IF(MINUTE(B2-A2)>0,MINUTE(B2-A2)&" minutes ","")

 

[jtakw]

Ok, I'm going to use B2 & A2 for the formula, otherwise, we'll have to Parse out the Text value of C2.

Try this:

=LOOKUP(INT(B2-A2)*24+HOUR(B2-A2),{4,8,12,28,32},{1,2,3,4,5})

 

[exc]

Ok awesome, that worked perfectly thank you!

 

[jtakw]

You're welcome, thanks for the feedback.

Additional comments I'd like to make.
Rather than using your current formula in C2 as you posted in Post #5, which produces a Text String, you can do this, which retains a Real Date/Time value:

Book3.xlsx
	A	B	C
2	4/14/2021 18:00	4/16/2021 17:00	1 days, 23 hours, 0 minutes
3			^ Custom Format like below
4			d "days, "h" hours, "m" minutes"
Sheet924

Cell Formulas
Range	Formula
C2	C2	=B2-A2

Edited just now.

 

[exc]

Awesome! That is an even easier formula for sure.

 

[jtakw]