What?
This is a quick note to myself so that I never use parentheses in the column headings again. Basically I have a pivot table in Microsoft Excel 2010 with the projects down the left (in the first column) and the days of the week along the top.

Why?
The excel report would hit a bug where it couldn't work out that 10 (Wednesday) happened after 8 (Monday).

How?
See the following screenshot and note the dates for Monday, Tuesday, Wednesday, and Thursday:


What?
A quick article on how to skip rows or even specify a range to extract from the Excel file when using as the DataSource.

Why?
If you are simply using a text file as your data source, then the options in the connection manager will let you skip rows and specify column datatypes. You don't get that with Excel but you can still control the data range.

How?
My example is that I have an excel sheet where the first 10 rows are descriptions to the column, and then I have 38 columns ("A" to "AL"):

What?
Please note: the following article is not a solution but a page of various methods and date conversions which I try and use depending on the situation.

This is a quick article on how I got time conversion working in an SSIS package which read from a text file. Note that the example below converts a string in European Date Format (ie. "ddmmyyyy" to "dd/mm/yyyy"). Also, my data flow imports two dates one which was imported as a string (DT_WSTR) and another as a integer (DT_R8).

Why?
It took me a long time to figure this and it was only by trawling through columns that someone mentioned that maybe all the data in that column is not consistent? This made sense as I need to parse the string to extrapolate the date, month and year.

How?

Applies to:
- Microsoft Windows 7 Enterprise
- User with Local Administrator Privileges (required for setup)


Why?
Our work has group policies and two of our programs, Visual Studio 2010 Ultimate (VS2010) and Business Intelligence Development Studio (BIDS), require elevated or administator rights/privileges when run. This is so that these programs can write back and forth to our C drive. If we don't run them as local administrators on the workstation, they error incessantly and never manage to do what you want them to do.


What?
I want to create a desktop shortcut that I can double-click and it will run the above programs in administrator mode without confirmation by the Windows operating system. At home this may not be a problem but this is intended for those in an office environment and we get prompted to login (with the same account as we're currently logged-in with???) instead of being able to just click on "Yes".


How?
The gist is that we're going to create a scheduled task (that won't be scheduled) and a shortcut to that task:

What?
We have an excel spreadsheet which reports against a mySQL database and reads time spent on projects by IT Service colleagues. The main report is a pivot table with staff members along the top, tasks down the first column, and time spent in the form of person days in the cross-join.

Why?
Currently the smallest bookable time by low-level tape monkeys and techies is 30 minutes (Managers it would appear can book whatever time, eg. 5mins). 30 minutes for us translates to 0.07 in person days (a person day being 7 hours and 24 minutes or 26640 seconds).

How?

What?
Applies to Windows 7.
Requires a Vogonian environment of red tape and bureaucracy.

This is an article to describe an alternative method then "right-clicking on the desktop", "selecting Personalize", "clicking on Desktop Background", "browsing to the Picture Location". The problem happens when browsing to the picture location and it doesn't seem to remember which folder you selected. At home, this would be a breeze, but that's because I don't implement group policies at home.

Why?
We get problems with this because none of our staff are full administrators of their local workstation. As IT staff we have elevated privileges but not absolute rights to configuring our computer. And we still need to re-login for every change. About a zillion group policies control our every deviation from the standard staff image and so we spend more time on "how do you make it like it was in XP?" situations.

How?
Who doesn't want to change their desktop background wallpaper? Using Windows 7, who doesn't want it to be a slideshow as well?

What?
Really not work related but if you know your sharepoint site:

Custom Error
http://<My_SharePoint_Site>/_layouts/MySite.aspx?Error=You%20are%20a%20jerk%20and%20SharePoint%20does%20not%20share%20with%20jerks.


Installer?

What?
I misunderstood what was going to happen when I started this. We have a SharePoint 2007 site and we have been asked to migrate links and calendar from an existing system. I was worried when following other instructions because I thought my personal calendar in Outlook 2007 would be completely shared with everyone who had access to the SharePoint site... It doesn't.

The following steps show you how SharePoint will open a shared calendar if you don't already have it in your Outlook 2007. Similar to you opening a shared calendar, the sharepoint calendar will sit separate to the others. Once it's in your Outlook, you can copy over events. The calendar is separate to your own and to other Exchange ones.

Eeek!

Applies to
  • Microsoft Office Sharepoint 2007
What?
I've been tasked with adding links to files located on some network shares. I want to use UNC paths such as \\myServer\myShare\myFile.doc. I could map the UNC path to a drive letter and then link to it (eg. I:\myFile.doc)but what if other users haven't mapped the same drive letter to the path? Then they won't be able to use the link.

Why?
As soon as someone suggested I setup WebDAV to do this, I thought I'd write this article to warn others that that's unnecessary and can simply open another can of worms (security-wise). There may be situations when this is better but I'm just trying to add a link to our Sharepoint 2007 portal.

How?

Why?
A friend asked me if there was a quick way to simply right-click on a folder in Windows Explorer and it would generate a text file with the contents of the directory he right-clicked.

What?
We can do this by adding an entry to the context menu (when you right-click on an object). The following is a method of adding this as a single command similar to how we add the "Command prompt from here" option (now built-in to Windows 7). I added this option for him in Windows 7 Ultimate using the system registry (see "How: for Windows 7" below).
copyraw
-- yield

a_subfolder_in_this_folder
a_file_in_this_folder.txt
another_file_in_this_folder.doc
  1.  -- yield 
  2.   
  3.  a_subfolder_in_this_folder 
  4.  a_file_in_this_folder.txt 
  5.  another_file_in_this_folder.doc 

How?
Category: Windows OS :: Article: 420

Thought I'd put a quick note here, I tried a fair few solutions that didn't work and then found this hidden away in a forum:

Quick Count
copyraw
=INT(SUMPRODUCT((A3:A1000<>"")/COUNTIF(A3:A1000,A3:A1000&"")))
  1.  =INT(SUMPRODUCT((A3:A1000<>"")/COUNTIF(A3:A1000,A3:A1000&""))) 

This returns the number of unique values in the range A3 to A1000 and excludes the blank/empty cells.

Display all Unique
Found this note on one of Microsoft Help sites:
Category: Excel :: Article: 418

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

user   data   find   display   code   file   creator   first   server   form   windows   joomla   need   table   list   would   source   mysql   files   zoho   time   database   name   order   version   error   date   page   script   uploaded   used   report   following   google   case   website   added   license   note   where   using   value   system   deluge   work   parameter   client   function   field   create   JoelLipman.Com

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.