Time question

ken2step

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

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

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
642
=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
642
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
642

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,020
Messages
5,834,986
Members
430,331
Latest member
Syed Yasir Hannan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top