# Time question

#### ken2step

##### Well-known Member
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.

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

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

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

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.

Anyone have any ideas????

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.

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

thanks Barry that works very nicely!!!

Replies
11
Views
1K
Replies
17
Views
965
Replies
3
Views
133
Replies
4
Views
388
Replies
6
Views
391

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.

### Which adblocker are you using?

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

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