# Help in Create SLAB Agints Time & AON

#### jawaharprm

##### New Member
Hi Team,

Please help to create slab agints Time & AON, if calculated time meets Aon given criteria should be tagged as "YES" in new coloum

1. if Criteria meets In "C" Coloum <=36 hours and in G Coloum <=2 shoud be update as Yes
2. if Criteria meets In "C" Coloum <=32 hours and in G Coloum <=5 shoud be update as Yes
3. if Criteria meets In "C" Coloum <=30 hours and in G Coloum >5 shoud be update as Yes

Sample file has enclosed for your reference..

Thanks
Jawahar

#### Attachments

• 9.9 KB Views: 3

#### vletm

##### Excel Ninja
jawaharprm
Post an Excel related Challenge for others to Solve (You must have a solution) Don't post questions here!
Did You send a challenge?
Do You have a solution?
...
or did You post a question?
>>> moved to Ask an Excel Question

Last edited:

#### jawaharprm

##### New Member
Sorry Sir, i posted wrongly here...

#### AlanSidman

##### Well-Known Member
1. In order for this to work, you need to have the data in Column C as numerical and not text. I inserted a column as C and placed this formula in it

= B2-A2 and formatted it as a number (decimal)

Then in Column I the formula =IF(AND(C2<=1.5,G2<=2),"Yes",IF(AND(C2<=1.333,G2<=5),"Yes",IF(AND(C2<=1.25,G2>5),"Yes","NO?")))

and copy down in both cases.

#### Attachments

• 16.7 KB Views: 1

#### jawaharprm

##### New Member
1. In order for this to work, you need to have the data in Column C as numerical and not text. I inserted a column as C and placed this formula in it

= B2-A2 and formatted it as a number (decimal)

Then in Column I the formula =IF(AND(C2<=1.5,G2<=2),"Yes",IF(AND(C2<=1.333,G2<=5),"Yes",IF(AND(C2<=1.25,G2>5),"Yes","NO?")))

and copy down in both cases.
Hi Sir,

Thanks for your Time.. if i use "(= B2-A2 and formatted it as a number (decimal)" this method the calculation of time getting wrong.
EX - End Date - "8/2/2021 2:40:06 PM" - Begin Date "7/30/2021 9:49:13 AM" = "76 h 50 mins" if use the above format it calculated for "3.20" hours that is reason i not used this format..

Thanks
Jawahar

#### AlanSidman

##### Well-Known Member
1. You cannot calculate difference in Text Format.
2. Your result is not hours but Days.
3. You will need to multiply by 24 to get 76.8481 hours in your scenario
4. You must have your dates in numerical format to do any comparison. See point 1.