Add missing hours and days in the data sheet
Dear Friends
I have a data sheet "attached" that contains a missing data ( missing hours and missing days), I used the Kutools to insert blank rows for the missing hours but I still have a problem that I can't solve.
I need the hour columns to contain the 24 hours of the day and also to insert the missing days rows.
please help me in that ....
Hello mostafa.elnazer and Welcome to Excel Forum.
In the attached file columns G:I fill in the missing rules, days and times then columns J:L match the temp, dewpt, and RH
The formula that populates column G is: =IF(ROW()=2,0,MOD(G1+1,24))
The formula that populates column H is:Formula:
Please Login or Register to view this content.
The formula that populates column I is: =TIME(G2,0,0)
The formula that populates columns J:L is:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Re: Add missing hours and days in the data sheet
thanks a lot JeteMc, I trying to apply the fix for my whole file, I will keep you updated with the results ... thanks again ...
Re: Add missing hours and days in the data sheet
Dear JeteMc, I have applied the same fix as you suggested but it seems that the date column is not matching the correct date sequence ...
because it continues the numbering as they are numbers not dates ... attached is the test file ...
Modify the formula in cell H2 to read: =IF(ROW()=2,DATE(2000,1,1),IF(COUNTIFS(H$1:H1,H1)<24,H1,H1+1))
While cell H2 is still selected change the formatting to Custom > Type: yyyymmdd
Double click the fill handle to copy the formula and formatting down the column.
Let us know if you have any questions.
Dear JeteMc, I have tried the formula for the columns from J to L and the file keeps saying that the excel resources is not enough and I have to add more memory (although I have 32 Gb ram and 32 processor cores) or I have to try 64bit version of excel, and since my last reply i'm trying to fix this error with no success, and when I reduce the number of rows calculated to 605 as in your example file, I got zeros along the Temp column (I don't know if the error causes this zeros or not).
do you have any fix for this ???
thanks alot in advance ...
As far as the zeros or blanks in columns J:L I believe that the fix for that is to:
1) Select column B
2) From the Data tab select Text to Columns
3) Select NEXT and NEXT
4) Select DATE and YMD
5) Select FINISH
As far as the memory issue I have an 8 year old, dual core computer with 3.00 GB of installed RAM. I am using the 32 bit version of Excel. I didn't run into any problems applying the formulas to the file attached which has 1393 rows.
Let us know if you have any questions.
ok, I will try and give you feedback ...
thanks a lot.
What !?!
Confirmation after 3 years!
Hope JeteMc has bought a new PC! Or still using a 11-year-old one!
Quang PT
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks