# Excel - date range includes specified time of day (better approach)

Given a set of DateTime ranges in excel like:

```
Start Finish
13/03/2012 10:00:00 14/03/2012 03:00:00
15/03/2012 08:30:00 15/03/2012 10:00:00
```

And some TimeSpan like:

```
Start Finish
07:00:00 09:00:00
```

How do you determine if a time interval falls within a specific date range?

This approach could be a start:

```
AND(B2 < DATEVALUE(TEXT(B2, "dd/mm/yyyy")) + TIMEVALUE("07:00:00"),
B3 >= DATEVALUE(TEXT(B3, "dd/mm/yyyy")) + TIMEVALUE("09:00:00"))
```

Though it relies on the fact that it can provide start / finish values explicitly, rather than two dates in any order. The start condition <= finish will do, but it looks like it's too complicated.

Is there a better way?

**Edit** : Bonus points for a simple approach to finding the percentage of a date range that falls within a time span

source to share

This should give the number of hours falling in a span of time (but it's definitely not easy!):

```
=MEDIAN(F2,G2+(G2<F2),MOD(B2,1)+(MOD(B2,1)<MOD(A2,1)))
-MEDIAN(F2,G2+(G2<F2),MOD(A2,1))
+(F2<G2)*(MOD(B2,1)<MOD(A2,1))*MAX(MIN(MOD(B2,1),G2)-F2,0)
```

If it's greater than 0, the date range falls within the time span, divide by B2-A2 for percentage.

eg. Date range: 6:00 PM - 9:00 AM, Time range: 7:00 AM - 7:00 PM returns 03:00, which is 20% of the date range.

source to share

It sounds like you are assuming the dates will always be on the same day, is that correct?

Try

`=AND(MOD(A2,1)<=F2,MOD(B2,1)>=G2)`

In percents

`=MAX(0,MIN(MOD(B2,1),G2)-MAX(MOD(A2,1),F2))/(B2-A2)`

`Update:`

If the date range can be unlimited, 1 day or many, then you can use this formula to get the total number of hours over time

`=(INT(B2)-INT(A2))*(G$2-F$2)+MEDIAN(F$2,G$2,MOD(B2,1))-MEDIAN(MOD(A2,1),G$2,F$2)`

which assumes that the time span does not cross midnight - if the time span **can** cross midnight, for example. could be 08:00 - 11:00 but could also be 22:00 - 03:00 then this formula should work

`=(F$2>G$2)*(B2-A2)+SIGN(G$2-F$2)*((INT(B2)-INT(A2))*ABS(G$2-F$2)+MEDIAN(F$2,G$2,MOD(B2,1))-MEDIAN(MOD(A2,1),G$2,F$2))`

source to share

Say date ranges in columns A and B and Timespan in F2 and G2. Apply the following formulas and drag down.

```
H2 = IF(AND($F$2>=RIGHT(A2,8),$F$2<RIGHT(B2,8),$G$2>RIGHT(A2,8),$G$2<=RIGHT(B2,8)),1,0)
```

Column H indicates whether this value is valid for a specific date range.

```
I1 = SUM(H2:H4)/COUNT(H2:H4)
```

I1 gives you a percentage

source to share