Forum Discussion
Solved
Pulling averaged data based on start and end dates in Excel
Hello! I have the start and end date of a product. I have separate data that i need to average for those dates. Basically, I need excel to read the start and end date in one table, pull the correspon...
amuench16 FIRST you have to convert that _time into a Date-Time which I used:
=LET(_time,$G$2:$G$473, DATEVALUE(LEFT(_time,10))+TIMEVALUE(MID(_time,12,12))+(MID(_time,24,1)&"1")*TIMEVALUE(MID(_time,25,2)&":"&RIGHT(_time,2)) )not sure why excel I couldn't get excel to recognize that UTC date-time stamp format
THEN I used and AVERAGE(FILTER()) combo:
=AVERAGE(FILTER($H$2:$S$473,($F$2#>=B2)*($F$2#<=C2),0))
amuench16 FIRST you have to convert that _time into a Date-Time which I used:
=LET(_time,$G$2:$G$473,
DATEVALUE(LEFT(_time,10))+TIMEVALUE(MID(_time,12,12))+(MID(_time,24,1)&"1")*TIMEVALUE(MID(_time,25,2)&":"&RIGHT(_time,2))
)not sure why excel I couldn't get excel to recognize that UTC date-time stamp format
THEN I used and AVERAGE(FILTER()) combo:
=AVERAGE(FILTER($H$2:$S$473,($F$2#>=B2)*($F$2#<=C2),0))
- Thank you! That worked perfectly!
