Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

finding missing data value when date and time are combine

0

i have large amount of data with one coloume comtain date and time combine of one month and second data contain temperature. but some of time are missing during recording in combined date and time formate.so how i can find missing value in large data.

raviranjan

excel
MS Office Version: 10
posted by: ravi1097 (rep: 4) on Wed Jul 13, 2016 at 12:41 pm
Answer
Discuss

Discussion

I couldn't find a single cell that had a missing time, just some that were at midnight, so they had zeros.

I imported it into Excel in order to look through it and Excel can sometimes convert a "no time" to zero.

But, can you give some examples of where there is no time so I know what to look for?
don (rep: 1989) Jul 13, '16 at 1:08 pm
pls attach the file what u do in my excell fine, then i say what is the problem.
ravi1097 (rep: 4) Jul 13, '16 at 1:24 pm
I didn't do anything with your file except look at it. Update your Question to provide some examples of where the time is missing so I can try to help.
don (rep: 1989) Jul 13, '16 at 1:31 pm
i have sand the excell file of my data ,pls fiind the attachment and find
ravi1097 (rep: 4) Jul 13, '16 at 2:00 pm
Jharkhand Jamshedpur 12-01-2015 01-01-2016.csv file attched intially
ravi1097 (rep: 4) Jul 13, '16 at 2:02 pm
I already looked at that and I could NOT find a missing time value.

Give me an example of one of this missing time values so I can look at it - tell me the row it is on or the date value for it or the temperature value for it so I can see an example of what is wrong.

I told you this already!

I do not think that any date time value is missing a time in your attached file.
don (rep: 1989) Jul 13, '16 at 9:12 pm
In the same attachement u see data are missing 12/1/2015 10:10 to 12/1/15 24:00;12/2/15 00:00 to 12/2/15 18:30 and many more.
ravi1097 (rep: 4) Jul 14, '16 at 12:13 am
U see after 13/12/2014 is show error
ravi1097 (rep: 4) Jul 14, '16 at 3:21 am
Add to Discussion

Answers

1
Selected Answer

I think I see the problem now.

The only thing that you can do from Excel is to sort the data to ensure it is in the correct order and then identify where there are issues, if any.

What I would do is to put this formula in cell C3 (not cell C2) and copy it down by double-clicking the bottom right of the cell with the formula.

=IF(A3-A2>0.000695,"Error","")

When that is done, sort or filter by the column with the formula. You can filter it (Data tab > Filter) and just show the cells that contain the text Error and then you will quickly know where there is missing data.

This will let you know if there is any missing data and where it is.

Update

I uploaded the file with the formula put into it.

posted by: don (rep: 1989) on Thu Jul 14, 2016 at 12:31 am
Discuss

Discussion

U see after 13/12/15 show error.
ravi1097 (rep: 4) Jul 14, '16 at 4:47 am
It doesn't show error for me there. Only at 12/2/2015, 12/4/2015, and 12/7/2015.
don (rep: 1989) Jul 14, '16 at 10:42 am
u can apply all the value from 1st to end ,if u see after 12/13/2015 itr show #value.
ravi1097 (rep: 4) Jul 14, '16 at 1:12 pm
12-12-2015 23:53 22.46557999   12-12-2015 23:54 22.42309952   12-12-2015 23:55 22.40863037   12-12-2015 23:56 22.34473038   12-12-2015 23:57 22.26856995   12-12-2015 23:58 22.19531059   12-12-2015 23:59 22.15517044 #VALUE! 12/13/2015 00:00:00 22.19075966 #VALUE! 12/13/2015 00:01:00 22.19964981 #VALUE! 12/13/2015 00:02:00 22.33646011 #VALUE! 12/13/2015 00:03:00 22.4190197 #VALUE! 12/13/2015 00:04:00 22.41049004 #VALUE! 12/13/2015 00:05:00 22.35935974 #VALUE! 12/13/2015 00:06:00 22.3012104 #VALUE! 12/13/2015 00:07:00 22.20862007 #VALUE! 12/13/2015 00:08:00 22.11292076 #VALUE! 12/13/2015 00:09:00 22.08897018 #VALUE! 12/13/2015 00:10:00 22.08630943 #VALUE!
ravi1097 (rep: 4) Jul 14, '16 at 1:21 pm
I don't get that error at all. I simply copied the formula I gave you into cell C3 and then copied it down for the rest of the data and I did not get any #VALUE! errors.
don (rep: 1989) Jul 14, '16 at 1:58 pm
pls provide ur mail id for sending my excell file where i have apply this formula and getting this error.
ravi1097 (rep: 4) Jul 14, '16 at 2:10 pm
No. Upload it to your original question and include a line of text saying that you updated it so I will know that you did.
don (rep: 1989) Jul 14, '16 at 2:14 pm
Jharkhand Jamshedpur 12-01-2015 01-01-201645.csv
pls find the attachment and go down and see after 13/12/2015 in c section of row.
ravi1097 (rep: 4) Jul 14, '16 at 2:17 pm
Look at the file I Uploaded. I just deleted column C and put back in the formula I gave you and copied it down.

I saved the file as an Excel file. a CSV file won't save the formulas.
don (rep: 1989) Jul 14, '16 at 3:30 pm
how u convert excel file (i tried but not doing)and which excel version u have to work and pls provide each step either in vedio or text.
ravi1097 (rep: 4) Jul 15, '16 at 2:58 am
Excel 2010 and here is a tutorial on getting the data into Excel. Import Text files (CSV) into Excel
don (rep: 1989) Jul 15, '16 at 11:51 am
i m using excel 2007. same process as 2010
ravi1097 (rep: 4) Jul 15, '16 at 11:57 am
yes
don (rep: 1989) Jul 15, '16 at 12:51 pm
final jan2015.xlsx

have to see this attachment and see at 1/1/2015 7:24 (446 postion) and after 1/1/2015 7:26 .it has error in time ,but it will not show.
ravi1097 (rep: 4) Jul 15, '16 at 3:54 pm
do you have a question? The file you uploaded looks good to me. ok moment
don (rep: 1989) Jul 15, '16 at 3:56 pm
Sorry, my mistake, use this formula:
=IF(A3-A2>0.000695,"Error","")

The 1 is replaced with 0.000695.
I updated my answer as well.
This formula says there is an error if the next row is more than a minute off.
don (rep: 1989) Jul 15, '16 at 4:06 pm
ok,thankyou for help.

goodluck
meet u again.....
ravi1097 (rep: 4) Jul 15, '16 at 4:33 pm
you're welcome! :)
don (rep: 1989) Jul 15, '16 at 4:41 pm
hello, i have call csv file but date will be showeing in / formet not -. pls tell me what is ur windows date formate .
ravi1097 (rep: 4) Dec 29, '16 at 11:22 pm
I don't know what you mean, please start a new question.
don (rep: 1989) Dec 30, '16 at 4:32 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login