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

3 answers

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



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.



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



In percents



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


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




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



All Articles