IF function greater and less than

[WhitSky]

Hello, community

I would like to ask for your help, i have a column that has random numbers from 1 to 12 and the format is Time (7:05). In an another column i try to create an if statement =IF(E2<3,RANDBETWEEN(4,8),IF(AND(E2>3,E2<8),RANDBETWEEN(30,40),RANDBETWEEN(8,14))), thus, if it is below 3 i want it to give me from 4,8 but it does not bring anything for the IF(AND which is 30 and 40.

Thank you in advance

 

[Joe4]

Welcome to the Board!

I am a bit confused by your description.
What exactly is in cell E2?
Is it a numeric entry, a time entry, or a text entry?
Where does your mention of time (7:05) come into this question?

 

[WhitSky]

Thank you,

In E2 i use this formula =RAND()*(12-1)/24+1/24) which gives me a number that i chance it in time and then i format it to appear as (3:41). Using the if formula i want to say that
1) if E2<3 give me a random number from 4 to 8
2) if E2>3 and E2<8 give me a random from 30 to 40
3) if E2>8 give me a random number from 8 to 14

I hope i made it more clear,

Thank you again

 

[Joe4]

It is important to understand how dates and times work in Excel.
Dates are actually numbers in Excel, specifically the number of days since 1/0/1900.
And time is just the fractional component of one day (i.e. 6:00 AM is really 6/24, or .250).

Excel formulas do not care how you have formatted your cell. They will always run on the underlying, unformatted value of the cell.
To see what this is, change the format of cell E2 to "General".
Then you will see the value as Excel sees your values. And that is what your formula is running against.
So you will need to adjust your formula accordingly.

 

[WhitSky]

Oh okay i understand now,

Thank you very much for your help!

 

[Joe4]

You are welcome.