In this case, the date will show similar to "Wednesday, 26 September 2012. The use of the characters indicate that what follows is a pattern for how the date should be formatted. You can modify the merge field so that it includes a formatting switch, in this manner: This presumes that the name of the data field, from Excel, is MyDate. If you display the field codes (instead of their results), the field usually looks similar to this: You can add a formatting switch to the merge field used in your document. You are in luck there is another way you can get the date format you want without worrying about any method of data transfer. If all this talk about conversion methods, OLE, DDE, and the rest has your head spinning, take a moment and breathe deeply. Make sure the Confirm Conversion At Open check box is selected.The General tab of the Options dialog box. Just exit from the merge and follow these steps: If you don't see the dialog box, then it means you need to configure Word so that it will present the dialog box. Word should display a dialog box that asks how you want to have the data transferred you should select either a DDE transfer or the Excel by Conversion option. You can change the data transfer method when you select the Excel workbook in the merge process. The default data transfer method is OLE (object linking and embedding), which means that the data is transferred as raw data and Word does it's best to format what it fetches. First, you could check to see what method Word is using to grab the information from the Excel workbook. Specifically, it appears as "09-23-2012" but should look like "Sunday, 23 September 2012." David is wondering how to get the formatting on the date that he needs. The date is formatted correctly in Excel, but when it is merged into the Word document it loses the formatting. Part of the information being merged is a date. Applications like that have decades of development and refinement: the chances of getting a catastrophic risk from a logic error is infinitesimal - and they're easier to use than Excel for this purpose.David is performing a mail merge using data stored in an Excel workbook. Taking a tip from the person who responded, Ive attempted to do a mail merge, however my knowledge of mail merges is extremely small.That said, Ive managed to create something that just might fit the bill. At first I tried to do this through a macro, but I wasnt that successful. I would keep the employee Time Sheet in Excel, perhaps but rely on a proper accounting back-end such as MYOB ( ) to do the calculation and store the data. Ive been working on a way to use an excel sheet to auto-populate PDF documents. You need to keep accrual totals for things such as leave and sick day entitlement and decrement them.Įxcel is not the tool to use to do this job! The logic becomes very complex, the spreadsheet will become very slow if there are a lot of employees, and the impact to the business from a logic error is potentially catastrophic. You will need to import the data into "Helper Columns" (which can be hidden) so that you can implement "Closing" logic to the data import (If the entry is for a date prior to last Thursday, and I already have data in this row, then do not import again.) to prevent the employee going back and "adjusting" last week's attendance. Then you need to make provision to store the raw data for up to seven years (in Australia you have to keep financial data that affects the employee's tax position for up to seven years I do not know which jurisdiction you are in, or what your retention requirements are, but there will be some!) And you should time-stamp the data with the date and time that you retrieved it. Some design considerations: When you retrieve the employee's time, you MUST also bring across the DATES. Then you put a row for each employee in the Pay Sheet, which draws the data from each employee's time sheet and computes their wages. So you place a set of 31 rows on the timesheet containing each day of the month, with columns for start time, break time, finish time, leave type etc. If you want each employee to fill in their own timesheet, and the Pay Lady (it's always a lady, men don't have the patenince to do this properly.) to calculate the wages, I would link from the Pay Sheet to the Time Sheet. Whether you link from Time Sheet to Pay Sheet, or from Pay Sheet to Time Sheet depends on which person you expect to do the most updating. What I would do is "link" the two sheets, so that one is simply drawing data from the other. You would have to track down, and update, every single worksheet. So my answer would be: "I wouldn't merge them!" The main reason being that the Time and Wages sheet presumably contains complex calculation logic that you do not want replicated into multiple instances (because if you do, you will get a maintenance nightmare when anything in the rules changes). That's what I suspected, which is why I asked the question. Ah! Two sheets with entirely dissimilar structures.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |