• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help in Create SLAB Agints Time & AON

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

  • TEST1.xlsb
    9.9 KB · Views: 3
jawaharprm
As You have read about Excel Challenges:
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:
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

  • TEST1.xlsb
    16.7 KB · Views: 1
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..

Please help on same..


Thanks
Jawahar
 
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.
 
Back
Top