time calculation

rami79

New Member
Joined
May 8, 2005
Messages
47
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
 

Some videos you may like

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
Joined
Oct 20, 2003
Messages
513
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
Joined
May 19, 2002
Messages
2,916
You need to use the code tags and repost your formula.
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
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
Joined
Jul 14, 2005
Messages
333

ADVERTISEMENT

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
Joined
May 8, 2005
Messages
47
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
Joined
May 19, 2002
Messages
2,916
Maybe try this:

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

Watch MrExcel Video

Forum statistics

Threads
1,118,674
Messages
5,573,597
Members
412,537
Latest member
Mohamed_5966
Top