Time question

ken2step

Well-known Member
Joined
Jan 9, 2003
Messages
632
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!
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Maybe:

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

HTH

Edited due to board interpreting as HTML tags.
 

ken2step

Well-known Member
Joined
Jan 9, 2003
Messages
632
=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

MrExcel MVP
Joined
May 28, 2004
Messages
4,237

ADVERTISEMENT

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.) :oops:
 

ken2step

Well-known Member
Joined
Jan 9, 2003
Messages
632
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

Well-known Member
Joined
Jan 9, 2003
Messages
632

ADVERTISEMENT

Anyone have any ideas????
 

grd

Board Regular
Joined
Oct 19, 2005
Messages
210
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,674
Messages
5,573,597
Members
412,537
Latest member
Mohamed_5966
Top