# 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

+3

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.

+2

source

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

+4

source

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

0

source

All Articles