Convertir lecturas de cada 15 minutos en una por día.

caralb

New Member
Joined
Oct 26, 2017
Messages
6
Tengo esta table de lecturas cada 15 minutos. Quiero convertirla a una por día, por ejemplo a las 09:00:00.
Qué debo hacer?

READINGDATEAVG
2016-01-01 00:00:00501.1322
2016-01-01 00:15:00501.1432
2016-01-01 00:30:00501.1235
2016-01-01 00:45:00501.1046
2016-01-01 01:00:00501.1276
2016-01-01 01:15:00501.1499
2016-01-01 01:30:00501.1407
2016-01-01 01:45:00501.1447
2016-01-01 02:00:00501.1361
2016-01-01 02:15:00501.1318
2016-01-01 02:30:00501.159
2016-01-01 02:45:00501.1746
2016-01-01 03:00:00501.1606
2016-01-01 03:15:00501.145
2016-01-01 03:30:00501.1512
2016-01-01 03:45:00501.1656
2016-01-01 04:00:00501.1748
2016-01-01 04:15:00501.2027
2016-01-01 04:30:00501.1875
2016-01-01 04:45:00501.1583
2016-01-01 05:00:00501.1706
2016-01-01 05:15:00501.2023
2016-01-01 05:30:00501.2142
2016-01-01 05:45:00501.187
2016-01-01 06:00:00501.1858
2016-01-01 06:15:00501.1862
2016-01-01 06:30:00501.1941
2016-01-01 06:45:00501.2324
2016-01-01 07:00:00501.2255
2016-01-01 07:15:00501.1902
2016-01-01 07:30:00501.2071
2016-01-01 07:45:00501.2401
2016-01-01 08:00:00501.2562
2016-01-01 08:15:00501.2178
2016-01-01 08:30:00501.2023
2016-01-01 08:45:00501.2183
2016-01-01 09:00:00501.2467
2016-01-01 09:15:00501.2579
2016-01-01 09:30:00501.2495
2016-01-01 09:45:00501.2348
2016-01-01 10:00:00501.2403
2016-01-01 10:15:00501.265
2016-01-01 10:30:00501.2674
2016-01-01 10:45:00501.2603
2016-01-01 11:00:00501.2475
2016-01-01 11:15:00501.2537
2016-01-01 11:30:00501.2685
2016-01-01 11:45:00501.2903
2016-01-01 12:00:00501.2854
2016-01-01 12:15:00501.268
2016-01-01 12:30:00501.2832
2016-01-01 12:45:00501.305
2016-01-01 13:00:00501.3136
2016-01-01 13:15:00501.3031
2016-01-01 13:30:00501.2968
2016-01-01 13:45:00501.2967
2016-01-01 14:00:00501.3072
2016-01-01 14:15:00501.3211
2016-01-01 14:30:00501.3192
2016-01-01 14:45:00501.3226
2016-01-01 15:00:00501.3244
2016-01-01 15:15:00501.3294
2016-01-01 15:30:00501.3394
2016-01-01 15:45:00501.3236
2016-01-01 16:00:00501.3239
2016-01-01 16:15:00501.3349
2016-01-01 16:30:00501.3519
2016-01-01 16:45:00501.3501
2016-01-01 17:00:00501.3506
2016-01-01 17:15:00501.3552
2016-01-01 17:30:00501.3633
2016-01-01 17:45:00501.3681
2016-01-01 18:00:00501.3605
2016-01-01 18:15:00501.3634
2016-01-01 18:30:00501.3834
2016-01-01 18:45:00501.3691
2016-01-01 19:00:00501.3759
2016-01-01 19:15:00501.3632
2016-01-01 19:30:00501.3758
2016-01-01 19:45:00501.3942
2016-01-01 20:00:00501.3962
2016-01-01 20:15:00501.3897
2016-01-01 20:30:00501.399
2016-01-01 20:45:00501.4101
2016-01-01 21:00:00501.4022
2016-01-01 21:15:00501.4007
2016-01-01 21:30:00501.4123
2016-01-01 21:45:00501.4044
2016-01-01 22:00:00501.4194
2016-01-01 22:15:00501.4293
2016-01-01 22:30:00501.4376
2016-01-01 22:45:00501.4217
2016-01-01 23:00:00501.4305
2016-01-01 23:15:00501.4445
2016-01-01 23:30:00501.4419
2016-01-01 23:45:00501.4388
2016-01-02 00:00:00501.4511
2016-01-02 00:15:00501.4524
2016-01-02 00:30:00501.4397
2016-01-02 00:45:00501.4591
2016-01-02 01:00:00501.4715
2016-01-02 01:15:00501.4758
2016-01-02 01:30:00501.482
2016-01-02 01:45:00501.4602
2016-01-02 02:00:00501.4667
2016-01-02 02:15:00501.4802
2016-01-02 02:30:00501.5069
2016-01-02 02:45:00501.5096
2016-01-02 03:00:00501.4975
2016-01-02 03:15:00501.493
2016-01-02 03:30:00501.4945
2016-01-02 03:45:00501.5166
2016-01-02 04:00:00501.527
2016-01-02 04:15:00501.5346
2016-01-02 04:30:00501.5241
2016-01-02 04:45:00501.5094
2016-01-02 05:00:00501.5164
2016-01-02 05:15:00501.5398
2016-01-02 05:30:00501.5619
2016-01-02 05:45:00501.5466
2016-01-02 06:00:00501.5378
2016-01-02 06:15:00501.5386
2016-01-02 06:30:00501.5583
2016-01-02 06:45:00501.5711
2016-01-02 07:00:00501.5625
2016-01-02 07:15:00501.5628
2016-01-02 07:30:00501.5552
2016-01-02 07:45:00501.5646
2016-01-02 08:00:00501.5869
2016-01-02 08:15:00501.6023
2016-01-02 08:30:00501.5922
2016-01-02 08:45:00501.576
2016-01-02 09:00:00501.5908
2016-01-02 09:15:00501.6023
2016-01-02 09:30:00501.6064
2016-01-02 09:45:00501.6081
2016-01-02 10:00:00501.6134
2016-01-02 10:15:00501.6018
2016-01-02 10:30:00501.6039
2016-01-02 10:45:00501.6196
2016-01-02 11:00:00501.642
2016-01-02 11:15:00501.6395
2016-01-02 11:30:00501.625
2016-01-02 11:45:00501.6222
2016-01-02 12:00:00501.6444
2016-01-02 12:15:00501.651
2016-01-02 12:30:00501.652
2016-01-02 12:45:00501.6549
2016-01-02 13:00:00501.657
2016-01-02 13:15:00501.6675
2016-01-02 13:30:00501.6767
2016-01-02 13:45:00501.6785
2016-01-02 14:00:00501.684
2016-01-02 14:15:00501.6789
2016-01-02 14:30:00501.6802
2016-01-02 14:45:00501.6859
2016-01-02 15:00:00501.7063
2016-01-02 15:15:00501.7094
2016-01-02 15:30:00501.6929
2016-01-02 15:45:00501.6932
2016-01-02 16:00:00501.7115
2016-01-02 16:15:00501.7325
2016-01-02 16:30:00501.7081
2016-01-02 16:45:00501.723
2016-01-02 17:00:00501.7329
2016-01-02 17:15:00501.7318
2016-01-02 17:30:00501.7484
2016-01-02 17:45:00501.7508
2016-01-02 18:00:00501.7476
2016-01-02 18:15:00501.7355
2016-01-02 18:30:00501.753
2016-01-02 18:45:00501.7728
2016-01-02 19:00:00501.7898
2016-01-02 19:15:00501.7685
2016-01-02 19:30:00501.7595
2016-01-02 19:45:00501.7991
2016-01-02 20:00:00501.7815
2016-01-02 20:15:00501.7943
2016-01-02 20:30:00501.8009
2016-01-02 20:45:00501.8235
2016-01-02 21:00:00501.8235
2016-01-02 21:15:00501.8104
2016-01-02 21:30:00501.8199
2016-01-02 21:45:00501.8252
2016-01-02 22:00:00501.8242
2016-01-02 22:15:00501.8508
2016-01-02 22:30:00501.8449
2016-01-02 22:45:00501.847
2016-01-02 23:00:00501.8419
2016-01-02 23:15:00501.8576
2016-01-02 23:30:00501.8726
2016-01-02 23:45:00501.8784
2016-01-03 00:00:00501.8692
2016-01-03 00:15:00501.8737
2016-01-03 00:30:00501.8761
2016-01-03 00:45:00501.8929
2016-01-03 01:00:00501.8812
2016-01-03 01:15:00501.8928
2016-01-03 01:30:00501.8887
2016-01-03 01:45:00501.8948
2016-01-03 02:00:00501.9
2016-01-03 02:15:00501.9034
2016-01-03 02:30:00501.9045
2016-01-03 02:45:00501.9067
2016-01-03 03:00:00501.9086
2016-01-03 03:15:00501.9273
2016-01-03 03:30:00501.9265
2016-01-03 03:45:00501.9245
2016-01-03 04:00:00501.94
2016-01-03 04:15:00501.9506
2016-01-03 04:30:00501.9212
2016-01-03 04:45:00501.93
2016-01-03 05:00:00501.9417
2016-01-03 05:15:00501.9498
2016-01-03 05:30:00501.9723
2016-01-03 05:45:00501.9477
2016-01-03 06:00:00501.9427
2016-01-03 06:15:00501.9681
2016-01-03 06:30:00501.9815
2016-01-03 06:45:00501.9485
2016-01-03 07:00:00501.9541
2016-01-03 07:15:00501.9751
2016-01-03 07:30:00501.9916
2016-01-03 07:45:00501.9621
2016-01-03 08:00:00501.9589
2016-01-03 08:15:00501.9709
2016-01-03 08:30:00502.0017
2016-01-03 08:45:00501.9962
2016-01-03 09:00:00502.0028
2016-01-03 09:15:00501.9843
2016-01-03 09:30:00502.0007
2016-01-03 09:45:00501.9989
2016-01-03 10:00:00502.0242
2016-01-03 10:15:00502.011
2016-01-03 10:30:00502.0099
2016-01-03 10:45:00501.9942
2016-01-03 11:00:00502.0184
2016-01-03 11:15:00501.9961
2016-01-03 11:30:00502.0255
2016-01-03 11:45:00502.0362
2016-01-03 12:00:00502.029
2016-01-03 12:15:00502.0106
2016-01-03 12:30:00502.046
2016-01-03 12:45:00502.0741
2016-01-03 13:00:00502.0272
2016-01-03 13:15:00502.0518
2016-01-03 13:30:00502.0671
2016-01-03 13:45:00502.0597
2016-01-03 14:00:00502.0709
2016-01-03 14:15:00502.049
2016-01-03 14:30:00502.0894
2016-01-03 14:45:00502.057
2016-01-03 15:00:00502.03
2016-01-03 15:15:00502.038
2016-01-03 15:30:00502.0789
2016-01-03 15:45:00502.1099
2016-01-03 16:00:00502.1208
2016-01-03 16:15:00502.109
2016-01-03 16:30:00502.0808
2016-01-03 16:45:00502.0705
2016-01-03 17:00:00502.1018
2016-01-03 17:15:00502.1115
2016-01-03 17:30:00502.1216
2016-01-03 17:45:00502.1195
2016-01-03 18:00:00502.1144
2016-01-03 18:15:00502.1211
2016-01-03 18:30:00502.1348
2016-01-03 18:45:00502.1453
2016-01-03 19:00:00502.1478
2016-01-03 19:15:00502.13
2016-01-03 19:30:00502.11
2016-01-03 19:45:00502.124
2016-01-03 20:00:00502.1618
2016-01-03 20:15:00502.1575
2016-01-03 20:30:00502.1431
2016-01-03 20:45:00502.1257
2016-01-03 21:00:00502.1497
2016-01-03 21:15:00502.1562
2016-01-03 21:30:00502.1811
2016-01-03 21:45:00502.1753
2016-01-03 22:00:00502.1712
2016-01-03 22:15:00502.1623
2016-01-03 22:30:00502.1669
2016-01-03 22:45:00502.1801
2016-01-03 23:00:00502.2028
2016-01-03 23:15:00502.1904
2016-01-03 23:30:00502.1952
2016-01-03 23:45:00502.1758
<colgroup><col width="192" style="width: 144pt;"> <col width="64" style="width: 48pt;"> <tbody> </tbody>
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
a que te refieres con convertirla en una por dia?

Quieres promediar hasta ese punto? extraer ese valor?
 
Upvote 0
Quiero tener una columa con las lecturas de todo el mes, por cada día a las 09:00AM.

Así:

1/1/2016
1/2/2016
1/3/2016
1/4/2016
1/5/2016
1/6/2016
1/7/2016
1/8/2016
1/9/2016
1/10/2016
1/11/2016
1/12/2016
1/13/2016
1/14/2016
1/15/2016
1/16/2016
1/17/2016
1/18/2016
1/19/2016
1/20/2016
1/21/2016
1/22/2016
1/23/2016
1/24/2016
1/25/2016
1/26/2016
1/27/2016
1/28/2016
1/29/2016
1/30/2016
<colgroup><col width="108" style="width: 81pt;"> <tbody> </tbody>
 
Upvote 0
Asumiendo que tus datos se miren asi:


Excel 2012
AB
1READINGDATEAVG
21/1/2016 0:00501.1322
31/1/2016 0:15501.1432
41/1/2016 0:30501.1235
51/1/2016 0:45501.1046
61/1/2016 1:00501.1276
71/1/2016 1:15501.1499
81/1/2016 1:30501.1407
91/1/2016 1:45501.1447
101/1/2016 8:15501.2178
111/1/2016 8:30501.2023
121/1/2016 8:45501.2183
131/1/2016 9:00501.2467
141/1/2016 9:15501.2579
151/1/2016 9:30501.2495
161/1/2016 9:45501.2348
171/1/2016 10:00501.2403
181/1/2016 10:15501.265
191/1/2016 10:30501.2674
201/1/2016 10:45501.2603
Hoja1

Puedes usar esta Macro:

Code:
Sub Goku()

Application.ScreenUpdating = False


Sheets(2).Range("A2:B" & (Sheets(2).Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row)).Delete
    
lRow = Sheets(1).Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    
    For iCntr = lRow To 2 Step -1
    If Cells(iCntr, "A") Like "*9:00*" Then
    Rows(iCntr).Copy Sheets(2).Rows(Sheets(2).Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1)
    End If
    Next
    
Sheets(1).Range("A1").Select
Application.ScreenUpdating = True


End Sub

Obtendrias un resultado similar a:


Excel 2012
AB
1READINGDATEAVG
21/1/2016 9:00501.2467
Hoja2




En otra hoja
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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
Back
Top