Converting data from 30 minute to 15 minute intervals

dfariba

New Member
Joined
Dec 12, 2021
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
Hello

I want to convert my data from 30 minute to 15 minute intervals, my data start from 12:20 AM to 11:50 PM (Namur weather) and I want to convert it to start from 12:00 AM to 11:45PM just like solar power data time (Likw solar power Data time that I upload). can any one help me how to do so?

Thanks for your consideration
Fariba
1639305898159.png
1639305955938.png
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,549
Office Version
  1. 2016
Platform
  1. Windows
Hi DFariba,

Any such change will of course be an approximation. You can say that your first set of values for 15 minute will always be the first set of values of 30 minute, because nothing better is available.

After that I've used MATCH and AVERAGEA on most cells, like Temp., so it averages the previous 15 minute value with the 30 minute value for the same, or latest time less than the 15 minute time. If it finds no match then it just uses the previous 15 minute time value.

Text fields like Weather it just looks for the latest matching time in the 30 minute list.

DFariba.xlsx
ABCDEFGHIJKLMNOP
130 Min IntervalsTemp.WeatherWind (km/h)HumidityBarometerVisibilty km15 Min IntervalsTemp.WeatherWind (km/h)HumidityBarometerVisibilty km
212:20:00 AM12Passing Clouds4.90.821004.92N/A12:00:00 AM12.00Passing Clouds4.900.821004.92N/A
312:50:00 AM11Clouds15.740.791005.75N/A12:15:00 AM12.00Passing Clouds4.900.821004.92N/A
41:20:00 AM10Clouds26.320.771006.331212:30:00 AM12.00Passing Clouds4.900.821004.92N/A
51:50:00 AM9Clouds36.710.761006.711312:45:00 AM12.00Passing Clouds4.900.821004.92N/A
62:20:00 AM8Clouds46.930.751006.93131:00:00 AM11.50Clouds15.320.811005.34N/A
72:50:00 AM7Clouds570.751007141:15:00 AM11.25Clouds15.530.801005.54N/A
83:20:00 AM6Clouds66.930.751006.93131:30:00 AM10.63Clouds25.930.781005.9412
93:50:00 AM5Clouds76.710.761006.71131:45:00 AM10.31Clouds26.120.781006.1312
104:20:00 AM4Clouds86.320.771006.33122:00:00 AM9.66Clouds36.420.771006.4213
114:50:00 AM3Clouds95.740.791005.75112:15:00 AM9.33Clouds36.560.761006.5713
125:20:00 AM4Clouds106.930.751006.93132:30:00 AM8.66Clouds46.750.761006.7513
135:50:00 AM5Clouds118.060.721008.06162:45:00 AM8.33Clouds46.840.751006.8413
146:20:00 AM6Clouds129.170.71009.17183:00:00 AM7.67Clouds56.920.751006.9214
156:50:00 AM7Clouds1310.250.681010.25N/A3:15:00 AM7.33Clouds56.960.751006.9614
167:20:00 AM8Clouds1411.310.671011.31223:30:00 AM6.67Clouds66.940.751006.9413
177:50:00 AM9Clouds1512.370.651012.37243:45:00 AM6.33Clouds66.940.751006.9413
188:20:00 AM10Clouds1613.420.641013.42264:00:00 AM5.67Clouds76.820.761006.8213
198:50:00 AM11Clouds1714.460.631014.46284:15:00 AM5.33Clouds76.770.761006.7713
209:20:00 AM12Clouds1815.490.621015.49304:30:00 AM4.67Clouds86.540.761006.5512
1st
Cell Formulas
RangeFormula
K2:P2K2=B2
K3:K20K3=AVERAGEA(K2,IFERROR(INDEX(B$2:B$50,MATCH($J3,$A$2:$A$50,1)),B$2))
L3:L20,P3:P20L3=IFERROR(INDEX(C$2:C$50,MATCH($J3,$A$2:$A$50,1)),L2)
M3:O20M3=AVERAGEA(M2,IFERROR(INDEX(D$2:D$50,MATCH($J3,$A$2:$A$50,1)),M2))
 

dfariba

New Member
Joined
Dec 12, 2021
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
Thanks for your response, It really help me.
 

Forum statistics

Threads
1,182,107
Messages
5,933,697
Members
436,905
Latest member
Ibraeh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top