excel - Display data from certain date, from large data set -

-

i've got set of data @ work, need analyze , summarize based on daily values. have 48 values each date; 1 each half hour.

i've set sumif , countif-system calcuate average values, need solution display data 1 date, can assess whether data complete (the supplier known have missing data in sensors)

as example, data looks like. have 6000 lines of time. come in chunks time time, never know how big data set get. last 1 2200 lines

11-03-2016 11:30:00:000     13,3    51,6    7,85 11-03-2016 12:00:00:000     14,9    51,6    7,85 11-03-2016 12:30:00:000     13,9    51,6    7,86 11-03-2016 13:00:00:000     14,9    51,7    7,87 11-03-2016 13:30:00:000     14,7    52      7,87 11-03-2016 14:00:00:000     14,7    52      7,88 11-03-2016 14:30:00:000     0,1     52,1    7,89 11-03-2016 15:00:00:000     0,1     52,2    7,89 11-03-2016 15:30:00:000     14,8    52,1    7,9 11-03-2016 16:00:00:000     14,8    52      7,9 11-03-2016 16:30:00:000     13,3    52      7,91 11-03-2016 17:00:00:000     12,8    51,9    7,92 11-03-2016 17:30:00:000     7,9     52      7,92

the dates formatted "general" , can't seem change type of date nor time-format.
right have column left of dates reading

=int(left("range",10))

to pull date text-formatted cell date. ideally i'd cell showing hours well, couldn't quite figure 1 out.

what i'd vba or other piece of code, have 1 cell enter date value want analyze, , run macro, , have 48 data points shown in seperately.
know it's possible set table , filter data through there, time consuming.

if can done without using macros (ie vlookup-like) i'm open suggestions, can't think of solution myself.

use following formula extract time:

=mid(a1,12,5)

this result in "hh:mm". replace 5 8 extract seconds well.

to reliably transform date text date format can use following formula:

=date(mid(a1,7,4),mid(a1,4,2),mid(a1,1,2))

regarding analysis of 1 date: assume cell f1 date input. create table following (assuming time column column a)

time             datetime 00:00:00:000     =a2&" "&$f$1 00:30:00:000     =a3&" "&$f$1 ...              ...

if enter date in f1 text in correct format, datetime column 1 dataset , can use simple sumif() complete task.

edit: have newly introduced powerquery addin. it's natively shipped excel 2013 , 2016 , can download free excel 2010. it's brilliant way repeatedly extract clean values unstructured data set. see here: introduction powerquery

edit 2: if you're trying find gaps in dataset try following approach: select column , apply following custom date format:

dd-mm-yyyy hh:mm:ss":000"

afterwards enter value 01/01/2016 00:00:00 in first , 01/01/2016 00:30:00 in 2nd row. pull down far want. vlookup in column next , filter results #n/a errors. have gaps in data. (maybe you'll need transform lookup-column text format first)


Comments

Post a Comment

Popular posts from this blog

java - Static nested class instance -

-
this question has answer here: java inner class , static nested class 23 answers i came across in java, know static nested class why create instance of 'static'. isn't whole idea of 'static' use without instance? understand concept of inner classes, why (and frankly how possible to) create 'instance' of 'static' member @ ? why this: 1- outerclass.staticnestedclass nestedobject = new outerclass.staticnestedclass(); 2- nestedobject.anestedclassmethod(); and not this: 1- outerclass outerinstance=new outerclass(); 2- outerinstance.staticnestedclass.anestedclassmethod(); use on inner classes, keyword static indicates can access inner class without instance of outer class. for example: public class outer { public static class inner { /* code here. */ } } with construction, can create instance of inn
Read more

c# - Bluetooth LE CanUpdate Characteristic property -

-
i building xamarin.forms cross platform mobile app, uses monkey.robotics bluetoth low energy functionality. connecting mbed based implimentation of custom gatt service . in xamarin c#, triggers characteristic. canupdate property in monkey.robotics? this standard example c# based on: if (characteristic.canupdate) { characteristic.valueupdated += (s, e) => { debug.writeline("characteristic.valueupdated"); device.begininvokeonmainthread( () => { updatedisplay(characteristic); }); isbusy = false; // spin until first result received }; isbusy = true; characteristic.startupdates(); } this has been working, since changed own custom gatt service connecting to, canupdate property false. property , how triggered? me debugging gatt service code. thanks monkey.robotics open sour
Read more

JavaScript - Replace variable from string in all occurrences -

-
ok, know if have character '-' , want remove in places in string javascript, ... someword = someword.replace(/-/g, ''); but, when applying array of characters, s not working ... const badchars = ('\/:*?"<>|').split(''); let filename = title.replace(/ /g, '-').tolocalelowercase(); (let item = 0; item < badchars.length; item++) { // below not work global '/ /g' filename = filename.replace(/badchars[item]/g, ''); } any ideas? /badchars[item]/g looks badchars , literally, followed i , t , e , or m . if you're trying use character badchars[item] , you'll need use regexp constructor, , you'll need escape regex-specific characters. escaping regular expression has been well-covered . using that: filename = filename.replace(new regexp(regexp.quote(badchars[item]), 'g'), ''); but , don't want that. want character class: let filename = title.replac
Read more