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