Working out the difference in times between two columns [Solved]

sailingdemon 11 Posts Thursday September 28, 2017Registration date November 13, 2017 Last seen - Nov 10, 2017 at 10:47 AM - Latest reply: sailingdemon 11 Posts Thursday September 28, 2017Registration date November 13, 2017 Last seen
- 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
See more 

Your reply

5 replies

ac3mark 9964 Posts Monday June 3, 2013Registration dateModeratorStatus July 19, 2018 Last seen - Nov 10, 2017 at 04:59 PM
0
Thank you
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 11 Posts Thursday September 28, 2017Registration date November 13, 2017 Last seen - 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 11 Posts Thursday September 28, 2017Registration date November 13, 2017 Last seen - 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 11 Posts Thursday September 28, 2017Registration date November 13, 2017 Last seen - 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 11 Posts Thursday September 28, 2017Registration date November 13, 2017 Last seen - 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
Respond to ac3mark