Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

IF question excel 2010 with time

0

I have made a calculation for my work here I need to know if I have a break or not.

The if function should look or between the start time  and end time is 4 hours.

If the result is less or equal than 4 hours  I need to get a value of 0:00, if it is more than 4 hours then I get the result 0:30. (result must be  as a timestamp)

In mine If function I let in this case: 

time of 15:00 subtracting 8:00 then passes the test if the value true when it must be false, the formula I7 (15:00) minus H7 (08:00) <= 4 is natural result than 7 false;

Value as well which is 0:00

Value - if - false than 0:30

Hope some one can solve my problem?

Thanks for help

excel
MS Office Version: Excel2010
posted by: silona (rep: 4) on Wed Aug 17, 2016 at 4:11 am
Answer
Discuss

Discussion

Is the problem the first IF statement that you describe or the second one? It's not clear what you want in the IF statement.
don (rep: 1989) Aug 17, '16 at 11:31 am
This is the problem:H7= 15:00  I7=08:00 (time stamps)
The if formule looks:
IF H7-I7<=4 (in this case not true) because the result = 7 and 
The if function must give as result 00:00
If the result was true a result was less then 4
The If function must give as result 00:30 (time stamp)
But the if function gives faulty results as you use a time stamp??
silona (rep: 4) Aug 17, '16 at 5:56 pm
Add to Discussion

Answers

0
Selected Answer

Use this formula:

=IF(H7-I7<=TIME(4,0,0),TIME(0,30,0),TIME(0,0,0))

Then format that cell using a Custom number format of mm:ss

posted by: don (rep: 1989) on Thu Aug 18, 2016 at 1:00 pm
Discuss

Discussion

Thanks for replying, but it doen't work?
I get a fault at (4,0,0),
This is the line in excel maybe it can explain the error?

  Week         Paid Work Conversie Work   Payed Paid   Nr Date Start-Time End Tijd Pause Work-time Time till worktime Time After Conversie Work time Work time               19:00 tot 19:00 19:00 worktime till 19:00 after 19:00       H7 I7                 Ma 33 8-08-16 15:00 18:00 0:00 3:00 1:00 1 0 1 34,98 0,00 Di 33 9-08-16 7:30 14:30 0:30 6:30 4:30 4,5 0 4,5 75,79 0,00                                           4,5 0 4,5 75,79 0,00
silona (rep: 4) Aug 18, '16 at 5:17 pm
Sorry for the bad lay out when I copied it looks good as two excel line. to explain th whole line
silona (rep: 4) Aug 18, '16 at 5:19 pm
I have added the file Gio-Help.xlsx
Hopefull someone can seemingly simple if function solve for me?
silona (rep: 4) Aug 18, '16 at 5:25 pm
Add to Discussion
0

I have added the file that will proberly clear the fault

posted by: silona (rep: 4) on Thu Aug 18, 2016 at 5:22 pm
Discuss

Discussion

You need to add this to your original question like the instructions told you to do. Once you do that and update your original question, delete this "answer" that you made.
don (rep: 1989) Aug 18, '16 at 5:41 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login