Monday, August 18, 2014

Optimizer Pro Virus Removal; iStart123 Virus Removal

How to: Remove Optimizer Pro; iStart123 Virus Removal

This article has been replaced.  See this improved Keyliner article:
Virus Cleanup Steps

> Historical

My daughter arrived at a website and got a popup, "A new version of Firefox is available.  Update now?".  She clicked yes and got two viruses - Optimizer Pro and iStart123.  If she were running IE or Chrome, the message would change to match that browser.  

Additionally, I suspect, but did not research or prove, but a new Video Plugin was also installed, known as "New Video Player", or "Video Player", or as "VPlay".  This is also a virus

As I had warned her in the past, if you arrive at a website and an unexpected message appears, no matter how legitimate, immediately close your browser; do not click Yes or No or even "X". As soon as she clicked Yes/Install, she knew it was a mistake, but the damage was done.

This article discusses how to remove these two viruses.  The same steps are used for both, with a minor difference for iStart123.  You will find the steps tedious, with some redundancy, but this brings the best results.

1. Pre-Download files.
On a non-infected computer, download the following programs and burn them to a CD (steps on how to burn are not detailed here; if needed, ask a knowledgeable friend to help).

Ideally, burn downloaded files to a CD -- not a pen drive; media should be Read-Only; this keeps some viruses from infecting the downloaded software and this is a reasonable precaution.  If a second PC is not available, the download may or may not work on the infected machine.  Some viruses are able to block downloads and can replace them with their own infected copies.

A.  Download MalwareBytes: Malwarebytes
Choose the free edition.  Save to the CD.

B.  DownLoad SuperAntiSpyware: superAntiSpyware
Choose the free edition; this is a legitimate program, despite its flaky name.   Save to the CD.

2. Disconnect the infected computer from the Internet.

* Important: Unplug the Cat-5 network cable or press your laptop's function key/other key to disable wireless (many viruses calm-down when not active on the internet)

3.  Install and run the MalwareBytes program.

Even though you downloaded the most recent version, there are database updates that should happen and the program will complain that it can't update (because you disconnected the workstation from the Internet).  This is OK.  Continue with a full system scan.

The scan will take an hour or more, depending on your data and disk.
Allow MalwareBytes to clean up anything it finds.  It will do a good job, but will miss some of the OptimizerPro on the first pass. 

4.  After the scan and cleanup, Reboot. 
If prompted to reboot (you likely will be asked), reboot, but leave the machine *off* the Internet.

5.  Open the Control Panel, "Programs and Features" (Add Remove Programs) and uninstall MalwareBytes (it conflicts with the next step). 

Reboot if prompted. 
Again, stay off the Internet.

6.  Install and launch SuperAntiSpyWare.
I always like to run multiple virus scans, from different (trusted) vendors.  Often, one company will find something the other does not.  In this case, SuperAntiSpyware (as of 2014.08) will find something missed by MalwareBytes.

It too will complain about not being able to update its database.  Ignore and run a full-scan.

When done, at least on my machine, it will find a few additional vestiges of OptimizerPro (and it may find other viruses that were missed by MalwareBytes). 

Allow it to clean all that it finds.

7.  Uninstall SuperAntiSpyWare.

8.  Enable your wireless or Internet connection.  Do not launch any browser sessions.

9.  Re-Install MalwareBytes.

This time, allow it to update its database/signatures. 
Do yet another Full-System Scan.  Yes, I know this is somewhat redundant and I did this out of an abundance of caution.  The previous steps likely killed the virus, but the newest database update may catch more on this (or other) viruses.

10.  After this final scan, I recommend un-installing MalwareBytes for a second time.

(I tend to use this program as a utility and have not allowed it to remain installed).  You should not leave this program and your other, normal anti-virus installed at the same time.  See the closing notes, below.

11.  If your browser's home page was hijacked to "iStart123" (see second illustration, "Quick Start", at the top of this article), continue with these next steps. 

If your browser was not hijacked, you are done and the Optimizer Pro virus should be removed -- Malwarebytes saves the day.

iStart123 Additional Steps

MalwareBytes and SuperAntiSpyware cleaned up the OptimizerPro and the iStart123 virus, but neither program completely cleaned the iStart123 hijack.  Follow these additional steps:

A.  On your Windows 7 or Windows 8 Desktop, locate all browser icons  (if you have IE, Firefox, or Chrome, all three icons will be damaged.

B.  For each desktop *or* taskbar icon, "other-mouse-click" the icon and choose "Properties".  Note the end of the Target field.  After the (.... .exe") name and closing quote, if you find a bunch of "crap" (numbers, letters, punctuation, etc.); this is the hijack.

Remove the appendages, removing all text after the .exe's closing quote.
You will have to do this on every Start Menu, Task Bar, and desktop icon that launches the browser(s).

In Windows 8, some of these icons are hidden (such as on the Start Page).  From a Tile, select "Open File Location".  Then, within that folder, "other-mouse-click" the shortcut and select Properties -- cleaning up from there.  Exact steps not detailed in this article, but these are standard Windows icons and tiles.

C.  Alternately you can do the following -- and in many respects, this is easier than editing each icon.  Fix one icon for each type of program (IE, Firefox, Chrome), as described above (usually on the standard desktop).

D.  Then, fearlessly delete (unpin from Start Menu, Unpin from the Tile Menu) all other browser icons, leaving the one repaired icon.  Then, from the Repaired icon, "other-mouse-click" and choose "Pin to Start" and "Pin to TaskBar" -- rebuilding the icons.

This completes the cleanup for iStart123.

Additional Comments:

My daughter was running MSE (Microsoft Security Essentials - the free virus scanner for Windows).  It clearly failed to detect this virus -- but on the other hand, she did click "Yes" (I approve - UAC approval), allowing the virus to install -- giving it full, administrative access to the computer.

Not recorded in the steps above, a full, MSE after-the-infection-scan failed to detect either of these viruses.  This saddens me.

Once again, MalwareBytes deserves credit for fixing the computer.  And even if you do not leave the program installed, they deserve a donation for a fine product.

Your comments are welcome.

Thursday, August 14, 2014

Solution: Excel YYYY Dates show as 1905

Problem: Excel date formatting YYYY shows date as 1905.  YYYY-MM dates show as 1905.

I have re-arranged this article, showing the final solution(s) at the top.  More detailed explanations follow.

Assume Cell A3 contains the date:  08/13/2014 21:21 and is formatted as Date Value.
Notice cell D3, which is supposed to show only they year, shows as 1905 when it clearly should be 2014, 

D3 was improperly assembled from a "sub-date," using a formula =B3 *and* it was formatted as a date-cell.  This is a mistake. 

Do not use intermediate or sub-dates for any date-calculation or format.  Always point to a full-date cell.  

Most people are trying to format a cell as 2013-08  (YYYY-MM) and they are trying to assemble the constituent parts "YYYY" + "MM" to get the result.  This will fail.  

YYYY Solution as Text:

In the last cell, D3, where you want a simple "YYYY" result, use this text-formula, where cell A3 is a date-cell.  See the next section for a YYYY-MM example.

  • Always point =Text() to real date  (Cell A3) - never to a sub-date; 
  • Format the YYYY cell as "general" or "text" -- *not as date* 
  • Do not point to intermediate cells, such as =Year()
  • A3 can be formatted in any date-format
In the top example, cells B3 (YYYY) and C3 (MM)
are not needed for intermediate =Text(  ) work
-- using them will lead you astray.

* By doing it, all YEARS will sort correctly,

YYYY-MM Solution as Text:

If you want cell D3 to show YYYY-MM, do the following:
  •  Always point =Text() formulas to real date  (Cell A3), never to a sub-date;
    Do not point to intermediate cells, such as =Year()
  • In cell D3, where you want the answer, assemble the YYYY-MM formula:


    where cell A3 is a real date-cell (regardless of how formatted).
  • Format the YYYY-MM column as "general" or "text"; *not as date* 

Other Possible Solutions:

Using intermediate sub-dates will work with these other formulas (see first two green), but involves another cell to calculate the intermediate values. 
Click for larger view

Note cell D6 forces the first day of the month ( ,1), then uses Excel's cell formatting to show only YYYY-MM.  This preserves the "date-ness" of the cell, but it still required sub-dates.

Click for larger view

How does 1905 happen?
Imagine a pivot table where the dates need to be categorized by Year-Month.

For example, if today's date was 2014.08.13, and you needed a column header displayed as "2014-08".  The trouble is the formula displays the year as 1905.

For example:
Consider the following, where a valid date in Column A (August, 2014), is broken down into two constituent parts:

  • A real date (e.g. =now() or any other date) is in cell A3.  The date must be a real date and not a text-string date.  If you can format it as different date-styles, it is a real date.
  • To illustrate, the year and the month are shown as separate "sub-date" formulas, where

    cell B3 displays the date as a Year using =Year()and
    C3 displays the Month, using =Month()
    These may be useful, but not for the formulas in this article.
  • In the last cell, D3, where you want the answer, attempt to assemble a new date (e.g. "2014-08") by using the intermediate date values "2014" and "08/8", as in
    "2014" & "-" & "08"
    ....  or

    =Text(B3, "YYYY") & "-" & Text(C3, "MM"))      shows as 1905-01
  • Formatting D3 as "Custom Date - YYYY-MM," shows the year as "1905-01". 
How to properly build YYYY-MM

For this article, the goal is to build a YYYY-MM date. 
(Update: See illustration above for other possible formulas)

Intermediate Year and Month columns, assembled as YYYY-MM, will fail with a 1905 date
(see the red formula, illustrated below):

Both of these fail:

=Text(B3, "YYYY") 

The key:
Do *not* point date calculations at sub-date fields (the red-squared formula).
Use the original (true) date for the assembly (see the blue formula). 
Format the formula with either General or Text, do not use a Date format

=Text(A3, "YYYY") & "-" & Text(A3, "MM")

Alternate: Using a Custom Format Picture Clause:

Alternately, ignoring everything above, format the cell with custom-date format.  This preserves the "date-ness" of the data -- but has the drawback that the entire date, including the day and time survive, even though the 'displayed format' only shows Year-Month:

Click for larger view
  • Highlight one or more cells.  Other-mouse-click, choose "Format Cells"
  • From the Category, choose "Custom"
  • In the "type" field (illustrated above in black highlight), type "YYYY-MM" (no quotes)

In the case of a Pivot table, this may not work for you -- the Pivot will still see all the individual dates and times (their granular dates and times) from the original data and will not group them properly, still seeing the days, not the months.

Why 1905?

Excel stores all dates as a sequence or serial number, which represents the number of days since Jan-1, 1900 or Jan-1, 1905 (Macintosh)  See this Microsoft article.

For example, the date used in the examples above has a decimal number "41864.92", where the fraction (".92") represents a fractional-part of a day -- e.g. the Time.  That is, 2014 is 114 Years * 365 = 41,000.

When the year is the only part of the date calculation, years like 2012, 2013, 2016, etc., will always be 1905 because the serial number ('2014') is well below the current date's 41,000 number:

In other words, when you convert a cell to =Year(), with a result of "2014", you are saying two-thousand fourteen days since 01-01-1900 -- which happens to be sometime in 1905.  The year can vary, depending on an obscure base-date option in Excel and if Macintosh; I do not have details on these, but I would guess 1909.

Other Keyliner Excel Articles:

How to use Excel VLookup
Return First Word, Last Word, SuperTrim
Parsing City State Zip
Writing your own User Defined Functions in Excel