mediatx Ask Excel and Charting Questions, Muse about Visualizations, Learn and Share - Discussion Forums - Chandoo.org en-US Sun, 19 May 2013 02:59:04 +0000 http://bbpress.org/?v=1.0.1 q http://chandoo.org/forums/search.php http://chandoo.org/forums/topic/big-issue-with-date-format/page/2#post-4398 Tue, 30 Nov 2010 10:50:47 +0000 Hui 4398@http://chandoo.org/forums/ <p>Try this XLS version<br /> <a href="http://rapidshare.com/files/434046074/Pradeepthota123Hui.xls" rel="nofollow">http://rapidshare.com/files/434046074/Pradeepthota123Hui.xls</a> </p> http://chandoo.org/forums/topic/big-issue-with-date-format/page/2#post-4397 Tue, 30 Nov 2010 10:30:54 +0000 pradeepthota 4397@http://chandoo.org/forums/ <p>thanks a lot hui I have learnt much from you and this sheet doesn't work in xls format I guess </p> http://chandoo.org/forums/topic/big-issue-with-date-format/page/2#post-4392 Tue, 30 Nov 2010 08:05:39 +0000 Hui 4392@http://chandoo.org/forums/ <p><a href="http://rapidshare.com/files/434024092/Pradeepthota123Hui.xlsx" rel="nofollow">http://rapidshare.com/files/434024092/Pradeepthota123Hui.xlsx</a> </p> http://chandoo.org/forums/topic/big-issue-with-date-format/page/2#post-4390 Tue, 30 Nov 2010 07:30:29 +0000 pradeepthota 4390@http://chandoo.org/forums/ <p>Hi Hui,</p> <p>Can you post the template link for the same please </p> http://chandoo.org/forums/topic/big-issue-with-date-format/page/2#post-4389 Tue, 30 Nov 2010 07:25:07 +0000 Hui 4389@http://chandoo.org/forums/ <p>F3: =IFERROR(OFFSET('r2'!$G$1,SUMPRODUCT((Date='r1'!F$1)*(Employee_ID='r1'!$A2),ROW(Employee_ID))-1,0),0)<br /> and copy across</p> <p>and </p> <p>F6: =IFERROR(OFFSET('r2'!$G$1,SUMPRODUCT((Date='r1'!F$1)*(Employee_ID='r1'!$A5),ROW(Employee_ID))-1,0),0)</p> <p>Copy across<br /> Then copy Row 6 to Rows 9, 12,15 etc </p> http://chandoo.org/forums/topic/big-issue-with-date-format/page/2#post-4388 Tue, 30 Nov 2010 07:15:31 +0000 pradeepthota 4388@http://chandoo.org/forums/ <p>Hi Hui,</p> <p>I was trying this way</p> <p><a href="http://rapidshare.com/files/434018109/Pradeepthota123.xls" rel="nofollow">http://rapidshare.com/files/434018109/Pradeepthota123.xls</a> as per the sheet but getting 0 </p> http://chandoo.org/forums/topic/big-issue-with-date-format/page/2#post-4387 Tue, 30 Nov 2010 07:10:54 +0000 pradeepthota 4387@http://chandoo.org/forums/ <p>No still D3 I am not getting a value it is showing as 0 it should show me the actual duration but no I have tried but it failed </p> http://chandoo.org/forums/topic/big-issue-with-date-format/page/2#post-4386 Tue, 30 Nov 2010 07:05:10 +0000 Hui 4386@http://chandoo.org/forums/ <p>Whoops</p> <p>D3 should be<br /> =IFERROR(OFFSET('r2'!$G$1,SUMPRODUCT((Date='r1'!E$1)*(Employee_ID='r1'!$A2),ROW(Employee_ID))-1,0),0)</p> <p>Is everyhthing else ok? </p> http://chandoo.org/forums/topic/big-issue-with-date-format/page/2#post-4384 Tue, 30 Nov 2010 06:49:36 +0000 pradeepthota 4384@http://chandoo.org/forums/ <p>in the columns now it is working but the problem is I am not getting the data for the formula<br /> =IFERROR(OFFSET('r2'!$G$1,SUMPRODUCT((Date='r1'!E$1)*(Employee_ID='r1'!$A3),ROW(Employee_ID))-1,0),0) where I need to get 610 the actual duration but its not showing </p> http://chandoo.org/forums/topic/big-issue-with-date-format/page/2#post-4383 Tue, 30 Nov 2010 06:49:01 +0000 Hui 4383@http://chandoo.org/forums/ <p>Can you post the file or email me ? </p> http://chandoo.org/forums/topic/big-issue-with-date-format/page/2#post-4381 Tue, 30 Nov 2010 06:06:40 +0000 pradeepthota 4381@http://chandoo.org/forums/ <p>Yeah I have used but nothing is working it is showing an error #name </p> http://chandoo.org/forums/topic/big-issue-with-date-format/page/2#post-4309 Fri, 26 Nov 2010 07:32:55 +0000 Hui 4309@http://chandoo.org/forums/ <p>Did you use the Post just above with v3</p> <p>As I found an error in an earlier version and changed the forula to fix it up </p> http://chandoo.org/forums/topic/big-issue-with-date-format#post-4306 Fri, 26 Nov 2010 07:27:05 +0000 pradeepthota 4306@http://chandoo.org/forums/ <p>Hi Hui,</p> <p>The earlier one didnot work for rhis sheet.</p> <p><a href="http://rapidshare.com/files/433209561/Empdetails1.xls" rel="nofollow">http://rapidshare.com/files/433209561/Empdetails1.xls</a></p> <p>can you please look at this and let me know why is not working </p> http://chandoo.org/forums/topic/big-issue-with-date-format#post-3988 Tue, 16 Nov 2010 13:40:24 +0000 Hui 3988@http://chandoo.org/forums/ <p>The solution I have presented will scale to 10,000 or more records on R2<br /> If you start to have more than 1000'ish rows on R1 you may start to see the spreadsheet slow down due to the large number of Sumproducts used.</p> <p>To expand:<br /> <strong>Page R1:</strong> Just select Rows 23:25 and drag down. all the formulas will adjust automatically. because your copying 3 rows make sure you drag down a multiple of 3 rows eg : 123 not 124 rows etc</p> <p><strong>Page R2:</strong> Add records to your hearts content. R2 uses named ranges to automatically extend and add new records.</p> <p>I have made a minor change which will enable the above to happen properly so use this as a base, not V2 from earlier today<br /> <a href="http://rapidshare.com/files/431192508/Pradeepthota_Hui_V3.xlsx" rel="nofollow">http://rapidshare.com/files/431192508/Pradeepthota_Hui_V3.xlsx</a></p> <p>Have a look at the named formulas on R2 and read about Offset at: <a href="http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/" rel="nofollow">http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/</a><br /> Sheet R1 uses Sumproduct to select the correct data from Sheet R2 and return it.<br /> <a href="http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/" rel="nofollow">http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/</a></p> <p>Please note that I have removed most of your merged cells, these can make more trouble than there worth. </p> http://chandoo.org/forums/topic/big-issue-with-date-format#post-3986 Tue, 16 Nov 2010 12:52:26 +0000 pradeepthota 3986@http://chandoo.org/forums/ <p>Hi Hui,</p> <p>What can i say wow!!!!!!!:) for your solution</p> <p>But can u please explain me how does this work for 10000 or more records.</p> <p>And also please explain the formula. </p> http://chandoo.org/forums/topic/big-issue-with-date-format#post-3979 Tue, 16 Nov 2010 06:56:18 +0000 Hui 3979@http://chandoo.org/forums/ <p>Have a look at<br /> <a href="http://rapidshare.com/files/431145383/pradeepthota_Hui_V2.xlsx" rel="nofollow">http://rapidshare.com/files/431145383/pradeepthota_Hui_V2.xlsx</a> </p> http://chandoo.org/forums/topic/big-issue-with-date-format#post-3978 Tue, 16 Nov 2010 05:52:26 +0000 pradeepthota 3978@http://chandoo.org/forums/ <p>Thanks hui for that it will save me lot of time every day don't know how to thanks you for this.But then I need to have a extra row in the that int he rows 2,3,4 I need info of Emp id 1111, and in 5,6,7 th emp id of 2222 I need to insert a row for each employee id that is the emp id should be merged with the third row and the next column should have shift in the first row, actual time int he second roe,log in the third row. Please help me on this.</p> <p>I will show like how it should look<br /> Emp Id Team Name Emp Name Details 1-Nov 2-Nov 3-Nov 4-Nov<br /> 11111 To Sad Shift Second<br /> time 7:30<br /> Log 0<br /> 2222 To Kri Shift First<br /> time 8:30<br /> Log 435 </p> <p>etc for many records </p> http://chandoo.org/forums/topic/big-issue-with-date-format#post-3977 Tue, 16 Nov 2010 04:25:33 +0000 Hui 3977@http://chandoo.org/forums/ <p>The Log Values are already in Rows 3,5,7,9,11 etc eg: D5, G11 etc</p> <p>The changes I made don't use any VBA code it is all with the formulas in the Cells </p> http://chandoo.org/forums/topic/big-issue-with-date-format#post-3976 Tue, 16 Nov 2010 04:09:47 +0000 pradeepthota 3976@http://chandoo.org/forums/ <p>Hey Hui,</p> <p>Thanks a lot<br /> That wonderful it could be very much useful to me and you saved my day.</p> <p>I need to insert a row completely after each employee id till the end of it and insert the log value for each date of the respective employee id.</p> <p>can you please help me on this with VBA CODE </p> http://chandoo.org/forums/topic/big-issue-with-date-format#post-3963 Mon, 15 Nov 2010 11:29:37 +0000 Hui 3963@http://chandoo.org/forums/ <p>Pradeepthota</p> <p>I have changed all the dates from Text to Dates</p> <p>Have a look at this<br /> <a href="http://rapidshare.com/files/430979434/pradeepthota_Hui_V1.xlsx" rel="nofollow">http://rapidshare.com/files/430979434/pradeepthota_Hui_V1.xlsx</a></p> <p>Can you let me know what you think?<br /> Do you need to insert extra lines? </p> http://chandoo.org/forums/topic/big-issue-with-date-format#post-3958 Mon, 15 Nov 2010 03:39:02 +0000 pradeepthota 3958@http://chandoo.org/forums/ <p>can any body help me on my previous question </p> http://chandoo.org/forums/topic/big-issue-with-date-format#post-3874 Wed, 10 Nov 2010 02:46:08 +0000 pradeepthota 3874@http://chandoo.org/forums/ <p>HI Hui,</p> <p>the data will be done for every month and the records are higher.</p> <p>For every new month i will get a new set of data.</p> <p>I want to transfer the Shift data and Log details for the appropriate date back to R1 for each day according to the name.</p> <p>if you can help me with formula and a macro that will be great learning and also help ful me reduce my work by half as it is a repetitive job every month </p> http://chandoo.org/forums/topic/big-issue-with-date-format#post-3860 Tue, 09 Nov 2010 12:37:08 +0000 Hui 3860@http://chandoo.org/forums/ <p>Pradeepthota</p> <p>1. You want to insert a Row after every merged Name in Sheet R1<br /> 2. You then want to transfer the Shift data and Log details for the appropriate date back to R1 for each day</p> <p>questions:</p> <p>Why do you want a macro to insert the rows? You only need to do that once and only have a few rows or do you have to do it regularly eg every month or will you clean out the data for a new month</p> <p>The data can be transfered with formula and doesn't need a macro</p> <p>Can you please clarify </p> http://chandoo.org/forums/topic/big-issue-with-date-format#post-3859 Tue, 09 Nov 2010 12:07:19 +0000 pradeepthota 3859@http://chandoo.org/forums/ <p>Please find the links</p> <p><a href="http://rapidshare.com/files/429761922/1.xls" rel="nofollow">http://rapidshare.com/files/429761922/1.xls</a> </p> http://chandoo.org/forums/topic/big-issue-with-date-format#post-3857 Tue, 09 Nov 2010 11:52:47 +0000 pradeepthota 3857@http://chandoo.org/forums/ <p><a href="http://www.2shared.com/fadmin/1705" rel="nofollow">http://www.2shared.com/fadmin/1705</a></p> <p><a href="http://www.2shared.com/document/E04Wg9Br/1_" rel="nofollow">http://www.2shared.com/document/E04Wg9Br/1_</a> </p> http://chandoo.org/forums/topic/big-issue-with-date-format#post-3856 Tue, 09 Nov 2010 11:49:04 +0000 Hui 3856@http://chandoo.org/forums/ <p>When you uploaded the file Rapidshare will give you a download link<br /> You have to post the link to the file here so we can see it </p> http://chandoo.org/forums/topic/big-issue-with-date-format#post-3855 Tue, 09 Nov 2010 11:30:35 +0000 pradeepthota 3855@http://chandoo.org/forums/ <p>Yeah I have done that in Rapid share and also 2shared </p> http://chandoo.org/forums/topic/big-issue-with-date-format#post-3854 Tue, 09 Nov 2010 11:04:01 +0000 Hui 3854@http://chandoo.org/forums/ <p>Can you post the link to your file </p> http://chandoo.org/forums/topic/big-issue-with-date-format#post-3853 Tue, 09 Nov 2010 10:35:55 +0000 pradeepthota 3853@http://chandoo.org/forums/ <p>Hi Hui,</p> <p>Sorry for that ,will do that next time and thanks for giving me the URL I have uploaded the sheet if some one can help me its great </p> http://chandoo.org/forums/topic/big-issue-with-date-format#post-3852 Tue, 09 Nov 2010 09:58:55 +0000 Hui 3852@http://chandoo.org/forums/ <p>Pradeepthota</p> <p>Please start a new post for new questions, that way people with relevant experience will assist. Often people won't go back to an ongoing post if they don't think they can add value, but if the subject has changed they will be unaware of the change</p> <p>You can have several active posts at once.</p> <p>To upload files have a read of: <a href="http://chandoo.org/forums/topic/posting-a-sample-workbook" rel="nofollow">http://chandoo.org/forums/topic/posting-a-sample-workbook</a> </p>