Time question

[ken2step]

If I have the time midnight (00:00) in cell A1, 23:00 in B1 and 06:00 in C1....what formula could I put in cell D1 that would tell me if A1 fell between the two times in B1 & C1?
thanks for any help!

 

[Tazguy37]

Maybe:

=IF(AND(A1 > C1,A1 < B1),TRUE,FALSE)

HTH

Edited due to board interpreting as HTML tags.

 

[ken2step]

Don't understand that one???? Could you explain the A1C1?

 

[ken2step]

=IF(AND(A1 > C1,A1 < B1),TRUE,FALSE)

But this gives False, but the answer should be true since 00:00 falls between 23:00 and 06:00.

 

[Tazguy37]

If it's backwards, just exchange the TRUE and FALSE. (It's 2 A.M., so it's a bit hard to tell what's between what.)

 

[ken2step]

Put 22:00 in A1 and the formula still does not work. What I am looking for is to determine if time in cell A1 falls between the times in B1 & C1. Easy until you bring in 3rd shift start times such as 23:00 and 06:00. I been banging my head on this for sometime with no satisfactory results.

 

[ken2step]

Anyone have any ideas????

 

[grd]

Yes, your problem is that time is denoted as a percentage of the day. ie 00:00 is 0%, 23:00 is actually 95.8...% and 06:00 is 25%. Therefore, whilst midnight may be between 23:00 and 06:00 it is not as far as percentages of a day are concerned.

This is because you are trying to measure times between two independent days which are not linked.

There are a couple of ways around this depending on what you actually want to do.

If it is a simple matter of being between 23:00 and 06:00 the following day you should treat the times as say a 48 hour clock. ie 06:00 would be in the second day and so actually be 30:00 and midnight would be 24:00. If you format the cells correctly they will still show the 24 hour clock but, in the case of the 06:00 time it will be 125%.

If it goes beyond the simple approach of two days maybe you should utilise the full "date/time" format by formatting the cells for dates and times. eg dd/mm/yyyy hh:mm

This would give you the result you are after.

 

[barry houdini]

You could use this

=IF(B1 >C1,OR(A1<=C1,A1 >=B1),AND(A1 >=B1,A1<=C1))

...or more opaque but shorter....

=(B1>C1)+(A1<=C1)+(A1>=B1)=2

 

[ken2step]

thanks Barry that works very nicely!!!