The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

    Mastodon

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now20140424-VMware-Fusion-6.0.3.-no-reclaimable
    More Photos

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,861 other subscribers

Archive for the ‘Excel’ Category

« Previous Entries
Next Entries »

Excel worksheet function – How do I get the weekday name of a date?

Posted by jpluimers on 2014/07/04

At first sight you’d think that getting the weekday name of a date in Excel is as easy as this simple example using the [Wayback/Archive] WEEKDAY function as an intermediate:

A1 cell: 1/8/2009
B1 cell: =TEXT(WEEKDAY(A1),"dddd")

This will, for the given date, print the corresponding day.

The outcome for the 1st of August 2009 (we do dd/mm/yyyy over here) is Saturday, and you might think it is the right way to do it.

Well, as [Wayback/Archive] AdamV explains in [Wayback/Archive] it is not: the outcome is OK on certain systems, but not OK on other systems.

The actual solution is even simpler, but before we go there, lets first explain what is potentially wrong with the above code:

  • A1 has a date value
  • =WEEKDAY(A1)
    obtains an integer value
    in this case: 7
  • =TEXT(7, "dddd")
    obtains the weekday name of the integer value 7
    in this case: SATURDAY
  • The latter is only because of two things:
    • First the way TEXT operates:
      =TEXT(B1,"dddd, yyyy-mm-dd")
      returns this full date:
      Saturday, 1900-01-07
      Which means that if you don’t fill in a month or year, it uses January of 1900.
    • Second:
      Excel thinks the 1st of January 1900 is a Sunday (actually, it is Monday)
      so for Excel, the 7th of January 1900 is a Saturday.

Excel has a [Wayback/Archive] bug where weekdays before the 1st of March 1900 are wrong (it is the famous 1900 problem, which somewhat is the inverse of the – also famous – [Wayback/Archive] 2000 is not a leap year problem) which won’t get fixed as [Wayback/Archive] Excel wants to stay compatible with Lotus-1-2-3 which also has the bug.

So it is by luck that Excel gets the above way right.

To make sure it is always right, just format your date as "dddd" as AdamV suggests:

=TEXT(A1,"dddd")

This is much shorter than the first example, and always works well.

--jeroen

PS – via: worksheet function – How do I get the day name into a cell in Excel? – Super User.

Posted in Excel, Office, Power User | Leave a Comment »

A few Excel printing tips I always forget

Posted by jpluimers on 2014/05/30

With most software, most of the time you don’t use the majority of the features.

So when you need a part of that majority, it is always hard to find.

For Excel, most of my printing is standard (if I print at all), so these two are particularly hard to remember:

–jeroen

Posted in Excel, Office, Office 2007, Office 2010, Office 2013, Power User | Leave a Comment »

Classic Menu for Office 2010 and 2013 Programs

Posted by jpluimers on 2013/11/04

As with Office 2007 and up, quite a few features – including menus and keyboard shortcuts – have been removed or changed.

Microsoft has many posts on them, some of which are here:

The menu part can be remedied with ease: Bring Back Classic Menus and Toolbars to Outlook 2010 and 2013 [WayBack].

Works like a charm, and you can purchase for individual Office programs, or for 3 suite combinations [WayBack].

–jeroen

Posted in Excel, Keyboards and Keyboard Shortcuts, Office, Power Point, Power User, Word | 1 Comment »

excel “not enough storage is available to complete this operation (exception from hresult: 0x8007000e (e_outofmemory))” – Google Search

Posted by jpluimers on 2013/04/16

So I won’t forget to research this:

excel “not enough storage is available to complete this operation (exception from hresult: 0x8007000e (e_outofmemory))” – Google Search.

Somehow this occurs with Excel and the .NET app only having a few dozen megabytes of memory in use, so the cause must be something a lot more simple than “out of memory”.

It is a complex export, but I might just be able to get this going using ADO.NET, and make sure it is not a 60+k rows or 60+k characters issue.

–jeroen

Posted in .NET, .NET 4.0, C#, C# 4.0, Development, Excel, Office, Power User, Software Development, Visual Studio 2010, WinForms | Leave a Comment »

How do you make Excel print those cell lines? – AfterDawn: Forums

Posted by jpluimers on 2013/03/04

Question: How do you make Excel print those cell lines?

Thanks seb32:

I’m assuming you want to print the grid…

  1. File menu,
  2. Page Setup,
  3. go to the Sheet tab,
  4. On that page, under Print, there’s a checkbox called “Gridlines”.

Note: the above is for Excel 2003; Print Gridlines in Excel shows that Excel 2007 and 2010 have slightly different settings.

–jeroen

via:

Posted in Excel, Office, Power User | Leave a Comment »

.NET/PowerShell: Get-Host, quick way to get CurrentCulture and CurrentUICulture

Posted by jpluimers on 2013/01/28

A quick and easy way of getting the CurrentCulture and CurrentUICulture is to use the get-host cmdlet from PowerShell.

This is what PowerShell 2.0 shows on my system:

C:\Users\jeroenp>powershell get-host

Name             : ConsoleHost
Version          : 2.0
InstanceId       : 1ce173fb-70a7-403b-a2bd-3800fe740f7c
UI               : System.Management.Automation.Internal.Host.InternalHostUserInterface
CurrentCulture   : en-IE
CurrentUICulture : en-US
PrivateData      : Microsoft.PowerShell.ConsoleHost+ConsoleColorProxy
IsRunspacePushed : False
Runspace         : System.Management.Automation.Runspaces.LocalRunspace

The SeaTools from Seagate can’t cope with that because they don’t manage the Resource Fallback Process properly.

My machine is on en-IE, as it is English, and USA as location.

The main advantage for me is to use the that it is a good mix between English and Dutch settings:

  • English language (so you get proper error messages that you can find back using Google)
  • USA as location (to force more search engines to use .com domains)
  • EUR money settings (most software in Western Europe expects EUR, but displays USD when using en-US)
  • decimal dot (far easier import/export with non-Dutch stuff)
  • DD/MM/YYYY date format (I tried ISO 8601 YYYYMMDD, but that breaks too much software)
  • 24 hour clock format (just as it should be)
  • comma list separator (too much software is not configurable to use a certain separator for CSV, especially Excel depends on the system settings for list separator and decimal)
  • metric system (just as it should be)

–jeroen

via: Get-Host.

Posted in .NET, CSV, Development, Excel, ISO 8601, Office, Power User, PowerShell, Scripting, Software Development | Leave a Comment »

vSphere 5.1 (ESXi 5.1) can run any hardware level since ESX Server 3.5

Posted by jpluimers on 2013/01/17

Last year, I missed this tiny sentence:

So in plain English, any VM that was generated on VMware ESX Server 3.5 or later can run atop ESXi 5.1 unchanged.

Which means it is a snap to move your VMs from older ESX / ESXi / vSphere versions as long as they are ESX 3.x or later.

In fact hardware version 7 has the widest compatibility amongst ESX/ESXi/vSphere/Fusion/Workstation/Player versions (see the table at the bottom).

The free version still has a 32 gigabyte physical RAM limit (people are still confused by the vRAM / Physical RAM distinction, especially since vRAM is not limited any more). Read the rest of this entry »

Posted in ESXi4, ESXi5, ESXi5.1, Excel, Fusion, Power User, VMware, VMware ESXi, VMware Workstation, Word | Tagged: , , , , , , , , , , , , | Leave a Comment »

Excel Grid lines: How do you make Excel print those cell lines? – AfterDawn: Forums

Posted by jpluimers on 2012/12/28

One thing I always forget (especially since the UI in Office 2007 changed quite a bit, but the idea below gets me going).

I’m assuming you want to print the grid…

  1. File > Page Setup,
  2. then go to the Sheet tab.
  3. On that page, under Print, there’s a checkbox called “Gridlines”.

–jeroen

via How do you make Excel print those cell lines? – AfterDawn: Forums.

Posted in Excel, Office, Power User | Leave a Comment »

F3 in Excel: Show all named ranges in Excel – via: Spreadsheet Audit & Maintenance Tip | Chandoo.org – Learn Microsoft Excel Online

Posted by jpluimers on 2012/11/30

In Excel I always got confused with named ranges, as I thought they were hard to track.

Not!

The F3 keyboard shortcut gives you a list of named ranges including name and location. Which makes it way easier to work with named ranges.

See the excellent post Show all named ranges in Excel: It even has an animated gif image that shows  you F3 in action.

–jeroen

via: Show all named ranges in Excel – Spreadsheet Audit & Maintenance Tip | Chandoo.org – Learn Microsoft Excel Online.

Posted in Excel, Keyboards and Keyboard Shortcuts, Office, Power User | Leave a Comment »

6 Excel keyboard shortcut pairs I didn’t know yet: select row/select column and insert current date/time(via: The Best Shortcut Keys in Microsoft Excel)

Posted by jpluimers on 2012/09/17

Learned a few Excel keyboard shortcut pairs today:

Shortcut Action
Ctrl+Spacebar Select columns
Shift+Spacebar Select rows
Ctrl+; Current date
Ctrl+Shift+: Current time
Ctrl+Shift+2 Format current cell as default date
Ctrl+Shift+3 Format current cell as default time

–jeroen

via:

Posted in Excel, Keyboards and Keyboard Shortcuts, Office, Power User | Leave a Comment »

« Previous Entries
Next Entries »