Report

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

Ask a question sailingdemon 11Posts Thursday September 28, 2017Registration date November 13, 2017 Last seen - Last answered on Nov 12, 2017 at 11:41 AM by sailingdemon
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
Helpful
+0
plus moins
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 11Posts 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.
Reply
sailingdemon 11Posts 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?
Reply
sailingdemon 11Posts 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
Reply
sailingdemon 11Posts 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
Reply
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!