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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Maybe:

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

HTH

Edited due to board interpreting as HTML tags.
 
Upvote 0
=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.
 
Upvote 0
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:
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,211,789
Messages
6,103,986
Members
447,888
Latest member
eaydogan

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
Back
Top