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

Posts
11
Registration date
Thursday September 28, 2017
Status
Member
Last seen
November 13, 2017
-
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 

1 reply

Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1572
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
Posts
11
Registration date
Thursday September 28, 2017
Status
Member
Last seen
November 13, 2017
-
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
-
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
-
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
-
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