Working out the difference in times between two columns

Solved/Closed

sailingdemon Posts 11 Registration date Thursday September 28, 2017 Status Member Last seen November 13, 2017 - Updated on Nov 11, 2017 at 03:49 AM
sailingdemon Posts 11 Registration date Thursday September 28, 2017 Status Member Last seen November 13, 2017 - Nov 12, 2017 at 11:41 AM

I have various times in Column B (Departure time), and another set of times in Column C (arrival time).

If the time in B is between 07.00 and 00.59 and the difference between the 2 columns is less than 1 hour I want the corresponding cell in column D to be value 0 (zero), but greater than 1 hour to be value 1.

If the time in B is between 01.00 and 06.59 and the difference between the 2 columns is greater than 2.5 hours I want the corresponding cell in column D to be value 0 (zero), but greater than 2.5 hours to be value 1.

Is there a way of achieving this please?

thank you in advance

Working out the difference in times between two columns
Difference between fat16 and fat32 - Guide
Difference between intranet and extranet - Guide
Vostfr et vf difference - Guide
Times attack - Download - Children
Display two columns in data validation list but return only one - Guide

1 response

Answer 1 / 1

Blocked Profile
Nov 10, 2017 at 04:59 PM

OK, so the syntax for the IF command is as follows:

=IF(LOGIC_TEST,TRUE,FALSE)

The syntax for AND is as follows:
=AND(logic1,logictest2) {if both are true, return true)

You can nest both them together, and you can nest multiple and.

So, you wish to have the following structure:
=IF(AND(AND(B1<7,B1>.59),c1-b1<1),0,1)

This is not an answer, but an instruction set so that you can learn how to manipulate formaula for yourself in the future, not just CUT and PASTE from here with no idea of what is happening!

BTW, I tested this, and if I place a 6 in B1 and a 7 in C1, and place this formula into D1, it returns a 1

Hav3 fun!

sailingdemon Posts 11 Registration date Thursday September 28, 2017 Status Member Last seen November 13, 2017
Nov 11, 2017 at 02:35 AM

Thats great, I can get it to work for simple numbers but if I try to put a time in it throws an error.

sailingdemon Posts 11 Registration date Thursday September 28, 2017 Status Member Last seen November 13, 2017
Nov 11, 2017 at 03:01 AM

I am new to Excel at anything other than basics, I have figured out all sorts of formulas to make my spreadsheet work and to be fair will not be doing anything similar on a regular basis.
Rather than nesting I am testing each part of the formula and building it up in columns, once working in their individual components I will try and nest them. So far I have got this:
=IF(AND(A1<=12,A1>=7),1,0) In C1
=IF(AND((A1<18.9,A1>12),2,0) in D1
I can get this to work with basic numbers.
I have tried substituting 12 with 00:00 as I work in 24Hr clock, or 7 with 07:00 (when doing this I have added a $ sign in front of the cell reference) and I get an error message thrown back.

Any ideas please?

sailingdemon Posts 11 Registration date Thursday September 28, 2017 Status Member Last seen November 13, 2017
Nov 11, 2017 at 11:20 AM

After a day wracking my brain, and playing around I have figured it out. Once I realised that excel actually 'sees' a time as a decimal (and will display it this way unless format set to Time HH:MM)

Thank you anyway, Simon

sailingdemon Posts 11 Registration date Thursday September 28, 2017 Status Member Last seen November 13, 2017
Nov 12, 2017 at 11:41 AM

Having tried the above I have found that the decimal number can change (possibly because of date?) the result is therefore inconsistent so I a no further forward.

Any help greatly appreciated.

Thank you,
Simon

Similar discussions

You have guessed the code too many times. Contact support

Facebook says I have been trying too often

Playstation 3 won't obtain the IP address

lenovo T470 power LED blink 3 time but it's not powering up

Similar discussions

Subscribe To Our Newsletter!