Need help completing an IF function with date & time as inputs

[Miskondukt]

Using the attached doc, and its legend tab, I need the following columns corrected as I am seriously tripping up on formats vs inputs for outputs.

1.) Column "N's" (OSAT) result based on the duration output of the preceding column "M" is:
<=4 = 40
5 = 30
6 = 20
>=7 = 10

2.) Column "Q's" (IUT) result based on the duration output of the preceding column "P" is:
<=18 == 40
>=19<=24 == 30
>=25<=48 == 20
>=49 == 10

The legend tab shows the info as well.

Let me know if anyone needs further explanations.

Can I not attach a spreadsheet? Pasting my cells into this table only paste the results, not the formula.

​

Assignment Received	Customer Contact	CC (Mins)	CC Grade	OnSite Arrival	OSAT (Hrs)	OnSite Arrival Grade	Initial Upload	IUT (Hrs)	IUT Grade	Final Upload	Final Upload Appr.
9/8/2013 16:40	9/8/2013 17:10	0:30:00	40	9/8/2013 20:40	4	10	9/10/2013 15:49	47	20	10/8/2013 15:53	10/30/2013 10:01

<colgroup><col span="2"><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

The grade column is where I cannot produce a viable formula (based on the legend and/or numbered items above) provided the data cells of the Date and Time format (restricted to this format).

 

[shyy]

Welcome to the Board!!

Look at my sig and check out MrExcel's HTML maker add-in

Not sure what you are looking for

you can use the formula
=IF(N2<=4,40,IF(N2=5,5,IF(N2=6,6,IF(N2>=7,7))))

1.) Column "N's" (OSAT) result based on the duration output of the preceding column "M" is:
<=4 = 40
5 = 30
6 = 20
>=7 = 10

you can use the formula
=IF(P2<=18,40,IF(AND(P2>=19,P2<=24),30,IF(AND(P2>=25,P2<=48),20,IF(P2>=49,10))))

2.) Column "Q's" (IUT) result based on the duration output of the preceding column "P" is:
<=18 == 40
>=19<=24 == 30
>=25<=48 == 20
>=49 == 10

 

[Miskondukt]

Thanks Shyy; however, the formula isn't VB and this work cpu isn't allowing me access to that download.

I've tried the formulas you provided (and a tone more variations), but the format of the input cells seems to effect the way the formula is written and produces the result.

The input for the IF functions is in the format (unavoidable) "mm/dd/yy & h:mm:ss" and when I am making the IF functions the answers are not coming out correct.

I've also tried:

`=IF(M6=0,0,IF(AND(NOT(A6),M6=0),0,IF(M6<=4,40,IF(M6=5,30,IF(M6=6,20,IF(M6>=7,10,"wrong")))))) `=IF(M7=0,0,IF(M7<TIME(5,0,0),40,IF(M7>=TIME(5,0,0)<TIME(6,0,0),30,IF(M7>=TIME(6,0,0),20,IF(M7>=TIME(7,0,0),10,"wrong"))))) <tbody> </tbody>

<tbody>
</tbody>

`=IF(AND(M7<=TIME(4,0,0),M7<TIME(5,0,0)),40,IF(AND(M7>=TIME(5,0,0),M7<TIME(6,0,0)),30,IF(AND(M7>=TIME(6,0,0),M7<TIME(7,0,0)),20,IF(M7>=TIME(7,0,0),10,"wrong"))))

<tbody>
</tbody>

We really can't post excel docs in an EXCEL help forum?

 

[shyy]

you can post docs its just that most people won't bother downloading them for personal reasons.

 

[Miskondukt]

Must have some number of posts to fulfill before being able to attach, because I do not see any attach icon or button.

The permissions say I cannot post attachments.

Quite the online oxymoron.

 

[shyy]

You can use sites like imageshack get the html code/board code and post it here