time calculation

rami79

New Member
I want to calculate the difference in time.
example:
col a= time sent and col b= time replied.

what i want to do is : if b-a is less than 1 hour and col C=1, then OK, OR if b-a is less than 4 hours and C=2 then Ok...

I tried to use this :=IF(OR(AND(B1-A1<TIMEVALUE("1:00"),C1=1),AND(B1-A1<TIMEVALUE("4:00"),C1=2)),"OK","Not OK?")

But it seems that it is not calcultating the time correctly, the formula is fine, i may think that it is a format issue. can you help

thanks

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

wwbwb

Well-known Member
How's this work for you.

=IF(AND(HOUR(B2)-HOUR(A2)<1,C2=1),"OK",IF(AND(HOUR(B2)-HOUR(A2)<4,C2=2),"OK","NOT OK"))

Seti

Well-known Member
You need to use the code tags and repost your formula.

RalphA

Well-known Member
Probably, your problem is using hours as a number, as in
B1-A1<1. Since Excel calculates time based on days, one hour must e expressed as 1/24, that is, as 1/24 of a day. Thus, the following formula works fine for:
A1=10:20
B1=11:10
C1=IF(OR(AND(B1-A1<1/24,C1=1),AND(B1-A1<4/24,C1=2)),1,0)

D1=

colbymack

Active Member

Your B1-A1 code would work if you multiply the result by 24 (=(+B1-A1)*24), or divide the corresponding evaluator by 24, as indicated by RalphA.

Otherwise, if you are not interested in minutes being a part of the evaluation, then WWBWB's post works. It should be noted that WWBWB's post will evaluate 4:01-3:59 as 1 hour, and is therefore Not OK, even though it was only 2 minutes of actual time.

HTH,
Colbymack

rami79

New Member
hey guys,
you are great, but it is still not working for a reason.

I have 4 columns
A - priority that could be 1 to 4
B - time submitted
C- time replied
D- where the formula should be.

Depends on the priority there is a delay in replying:
example, if it is priority 1, the delay should be 1 hour max
if it is priority 2 , the delay is 6 hours
if it is priority 3 the delay is 24 h.

I need In column D to have the result OK, if there criateris are met for priority 1, 2 and 3.

I tried all of them, but i think that i miss somethig

thanks

Seti

Well-known Member
Maybe try this:

=IF(24*(C1-B1)<CHOOSE(A1,1,6,24,0),"OK","")

Replies
2
Views
68
Replies
9
Views
126
Replies
3
Views
108
Replies
10
Views
152
Replies
8
Views
146