Formula assistance

[maltait]

Hi All,

I have some data where I need to have an if statement that if time is over a certain figure that time is deducted. Also if time is under a certain time no changes.

Cell A1 - 05:50:45
Cell A2 - 07:36:32
Cell A3 - 03:10:00
Etc

What i need to do is have a formula that works out the following: where time is under 6 hours, 10 minutes is deducted from the time. Where time is over 6 hours 50 minutes is deducted. The new time can be in a new cell.

Any help qoukd be appreciated. Thank you

 

[Eric W]

Try:

Book4 (version 1).xlsb
	A	B
1	5:50:45	5:40:45
2	7:36:32	6:46:32
3	3:10:00	3:00:00
Sheet12

Cell Formulas
Range	Formula
B1:B3	B1	=A1-TIME(0,10+(A1>TIME(6,0,0))*40,0)

 

[maltait]

Try:

Book4 (version 1).xlsb
	A	B
1	5:50:45	5:40:45
2	7:36:32	6:46:32
3	3:10:00	3:00:00
Sheet12

Cell Formulas
Range	Formula
B1:B3	B1	=A1-TIME(0,10+(A1>TIME(6,0,0))*40,0)

That is great thanks Eric, have an awesome day!!!!!

 

[Eric W]

Happy to help! ?