Improve company productivity with a Business Account.Sign Up

x
?
Solved

Finding missing dates.

Posted on 2016-09-27
4
Medium Priority
?
75 Views
Last Modified: 2016-11-06
I have file with some data for 1 year. The time gap is 15 minutes as you can see in the file. However some of the data are missing and I would like to have an easy way to find it out. I would like to have the missing date,time in the respective row. Is it possible?
Can anybody please help me?
I have attached the file.

Thanks in advance.
upload.csv
0
Comment
Question by:Prashansa Shrestha
  • 2
4 Comments
 
LVL 7

Assisted Solution

by:D Patel
D Patel earned 1000 total points
ID: 41817739
Hi Prashansa,

You can take reference from the sheet which I have prepared to incorporate logic in your sheet.

Instead of writing VBA it will be easier for you to find the missing dates using Array Formulas.

You can find the attached sheet.
Missing-Dates-example.xlsx
0
 
LVL 7

Expert Comment

by:D Patel
ID: 41817763
You also refer the file attached herewith (which matches your requirement) : (Using VBA)
FindMissingDate-15minutesInterval.xls
0
 
LVL 35

Accepted Solution

by:
Rob Henson earned 1000 total points
ID: 41817777
Open the file in Excel and the Date and time will potentially show in one column with the results for each slot then in columns B C & D.

In a separate sheet, recreate the Date and Time columns with all entries.

In cell A2 just type the date 01/01/2014, in A3 type =A2 and copy down as far as row 97. In A98 then put =A2+1 and copy down to row 35041  (24hrs x 4 time slots x 365 days = 35040 entries)

In Cell B2 type 00:00:00, B3 type =B2+TIME(0,15,0) and copy down as far as row 97. In B98 type =B2 and copy down again as far as 35041.

Obviously you could do the formula in A3 to B97 at the same time ad then do A98 and B98 and copy them both down.

In columns C D & E row 2 then put this formula:

Col C =IFERROR(VLOOKUP(TEXT($A2+$B2,"dd.mm.yyyy hh:mm"),upload!$A:$D,2,FALSE),"")
Col D =IFERROR(VLOOKUP(TEXT($A2+$B2,"dd.mm.yyyy hh:mm"),upload!$A:$D,3,FALSE),"")
Col E =IFERROR(VLOOKUP(TEXT($A2+$B2,"dd.mm.yyyy hh:mm"),upload!$A:$D,4,FALSE),"")

and copy down the full extent.

Now apply a filter to the data and select column C to show only blank. Those are your missing values.

If you remove the filter and copy, paste values; you will have a replicated version of the data with missing date/time entries inserted.

See attached.
missing-entries.xlsx
0
 
LVL 14

Expert Comment

by:frankhelk
ID: 41875972
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Split:
-- Rob Henson (https:#a41817777)
-- D Patel (https:#a41817739)


If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

frankhelk
Experts-Exchange Cleanup Volunteer
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…

587 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question