Excel won’t recognize dates

Help, Excel won’t recognize dates! This is frustrating sometimes, even when you right click on a cell and give it the correct format. In my case I have a script that exports the last logon dates from Active Directory and they’re formatted as DD/MM/YYYY. So for instance the 25th of March 2016 would be 25/03/2016. Very simple!

When trying to filter it, you’ll see them all in there and no Data Filtering is available:

lastlogon-unformatted

The funny thing is.. I also have a last password set field which is formatted the same way and that works just fine. I’m assuming the 01/01/1601 is driving Excel mad? 🙂

Either ways, this workaround I found seems to work all of the times:

  1. Select the whole column.
    • excel-select-column
  2. Click on Data on the top ribbon and then click Text to Columns.
    • excel-data-textTocolumns
  3. Select Delimited and click Next.
    • excel-textTocolumns-Delimited
  4. Now make sure that nothing is selected and click next.
    • excel-textTocolumns-delimiters
  5. Select Date and select DMY (or whatever format you have in your column) and then click Finish.
    • excel-textTocolumns-date-DMY

(more…)

Read More

Prevent users from using PST

This article, I think, it’s pretty interesting as I’ll show you how to set up a new GPO to prevent users from using PST files in Microsoft Outlook without the need to install any Office ADM Templates.

All the GPO will do is create/modify a few registry keys (one for each version of Microsoft Office). There are two ways, depending on your environment you can choose either one or the other. The first one it’s the cleanest way to do it but it’s only supported from Windows 7 and above (and from Windows Server 2008). The second one it’s a bit more “rusty” as it basically launches a batch file and that’ll do the work. Both worked for me but at the end I chose to use option number 1 as the few XP/Vista machines we have, are about to go.

Note that I want to prevent users from growing their current PST files, this GPO will still allow them to access their existing PST files. I think this is very important. Also, they’ll be allowed to create new PST files and to attach existing ones at a later stage but they won’t be able to modify them (only create subfolders which is kind of useless).

Before showing you the configuration of both the GPOs, I will show you the core of how to prevent users from using PST files in Outlook by creating a new registry key for each Office version. For instance, if you want to deny Outlook 2016 users to add new items to their PST files, all you need to do is create this new registry Key:

And then create a REG_DWORD (value set to 1) named PstDisableGrow. You can do the same with any Office version, I went down to 11.0.

Important: This only works on the HKEY_CURRENT_USER Registry! So you must run it with the end-user account.

You can add these keys with a batch file with just a line for each version [Notice how I used echo Y | – This will skip the confirmation as it’s going to “press” it for you].

Or remove them once you want to allow the users to fully utilised PST files.

For both the GPOs, I am creating a group in Active Directory that will contain all users I want to allow PSTs for. Also, both the GPOs have been set to run only on workstations, avoiding Servers:

GPO - WMI Filtering Workstation - WMI Filter

Option #1 – Using only the standard GPO Editor (From Windows Server 2008+) for Windows 7+ clients.

  • Create a new GPO and Edit it – I called it Disable PST Grow.
  • Navigate to User Configuration\Prefernces\Windows Settings\Registry.
    • Disable PST Grow GPO Registry
  • Right click on Registry and select New > Registry Item.
  • Under Action, select Update. Fill in the rest as per the below screenshot.
    • Disable PST Grow GPO Registry - Update - General

If you also want to have an exception group, keep on reading the extra few steps. (more…)

Read More

Lync 2013 silent install

Performing a Lync 2013 silent install isn’t as easy as it was with Lync 2010, especially if the application you’re trying to install was downloaded from the Volume License website.
I’m not sure if the issue I had trying to perform a Lync 2013 silent install applies to all version of Lync 2013. The problem I had was that after I created a customised MSP file (keep reading to see how to do that) and started the installation silently, Lync was not there! I could see it in Add/Remove Programs, but I couldn’t see it anywhere else. Not even when I searched in Start: this problem occurred on both Windows 7 and 8.1. Note that I’ve only tried with the 32bit version (also SP1) as that’s the one I need to deploy (also to machines running Windows x64).

After searching for a while I finally got to a download page on the Microsoft website:
http://www.microsoft.com/en-us/download/details.aspx?id=35554

Basically this file contains updated Office Customization Tool (OPAX/OPAL) files for Microsoft Office 2013.
Once you have downloaded the file, run it (it’s a self extractor .exe file) and choose the location where to extract the files.
Navigate to the location where you extracted those files and copy the admin folder in the root of the Lync 2013 installation folder and overwrite all existing files and folders.

Microsoft Office Customization Tool admin folder

Once you’ve done that, you’re good to re-create the MSP file for a customised Lync 2013 silent install. If you already have the old MSP file you created earlier with the original Lync 2013 installation, you must import it and save it back again.

If you’re here just to see how to create a Lync 2013 silent install MSP file, this is the moment 🙂 .
Let’s suppose the content of the Lync 2013 installation is in C:\temp\Lync 2013\x86.

Run the following command (you can also do it in “Run”):

This will open up the tool that will allow us to customise the installation. You’ll be prompt to select the version of the application you want to customise and then you’ll be good to start using the Microsoft Office Customization Tool.

Microsoft_Office_Customization_Tool_Select_Lync_Installation

What I generally modify is the Organization name under Install location and organization name.

Microsoft_Office_Customization_Tool_OrganizationName

This is the most important part which will allow Lync to be installed silently. Firstly choose whether you’re going to use a KMS server or just type the product key in. Secondly, you want to accept the terms and conditions and finally select None as Display Level (or just make sure the Suppress modal is selected).

Microsoft_Office_Customization_Tool_KMS-AcceptTerms-Suppress

(more…)

Read More