The Wiert Corner – irregular stream of stuff

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

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

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

    Join 2,573 other followers

Archive for the ‘Excel’ Category

Some links on Excel format strings

Posted by jpluimers on 2022/01/25

In Get Formatted Value of Cell in Excel – Stack Overflow, I focused on the =TEXT function, then indicated I would look into Excel format strings later.

Below are just a few links and a very short description as hopefully later I will have more time to dig into this.

The basic format is this (where all bits other than Format1 are optional):

[Locale]Format1;Format2;Format3;Format4

For now this is for my link archive:

  • [Wayback] Excel number format strings (e.g. “@”, “$-409]d-mmm-yy;@”) – Complete reference availability? – Stack Overflow has two answers of which the second one is deleted, but very useful:
    1. Here’s a list of the Number Format Codes for Excel.
      • Second option for link: Ecma-376 4th Edition Part 1, Section 18.8.31
    2. If you are looking for the international number codes try at http://office.microsoft.com/en-gb/excel-help/creating-international-number-formats-HA001034635.aspx
  • The HA001034635 link has disappeared, which is a pity as it explains the localisation; luckily two forms of the URL have been saved where [Locale] is constructed like this example (all Locale digits are hexadecimal):

    [$-24050412]m/d/yy

    In the preceding example:

    24  is the numeral shape component (Korean 1).
    05  is the calendar type component (Korean (Tangun era)).
    0412  is the locale and language designation component (Korean).

    1. [Wayback] Archive.is: Creating international number formats – Excel … HA001034635.aspx
    2. [Wayback] Archive.is: Creating international number formats – Excel – Office.com … HA001034635.aspx?redir=0
  • More [Locale] information is explained in [Wayback] What does the 130000 in Excel locale code [$-130000] mean? – Stack Overflow by [Wayback] User IrwinAllen13 – Stack Overflow and has more calendar formats than the above Microsoft links.
  • [Wayback] Number format codes – Excel for Mac has the base and sort of explains what the @ is for: the content of the cell.

    First the base:

    When you create custom number formats, you can specify up to four sections of format code. These sections of code define the formats for

    1. positive numbers,
    2. negative numbers,
    3. zero values, and
    4. text,

    in that order. The sections of code must be separated by semicolons (;).

    The following example shows the four types of format code sections.

    Four types of format code sections

    Callout 1 Format for positive numbers

    Callout 2 Format for negative numbers

    Callout 3  Format for zeros

    Callout 4  Format for text

    Then on on specifying less than 4 sections:

    • If you specify only one section of format code, the code in that section is used for all numbers.
    • If you specify two sections of format code, the first section of code is used for positive numbers and zeros, and the second section of code is used for negative numbers.
    • When you skip code sections in your number format, you must include a semicolon for each of the missing sections of code.
    • You can use the ampersand (&) text operator to join, or concatenate, two values.

    Then on the @ sign (which is under “Text and spacing”):

    To create a number format that includes text that is typed in a cell, insert an “at” sign (@) in the text section of the number format code section at the point where you want the typed text to be displayed in the cell.

    For example, to include text before the text that’s typed in the cell, enter “gross receipts for “@ in the text section of the number format code.

    Finally it explains how to colorise the formatting or hide particular values under “Decimal places, spaces, colors, and conditions”:

    The color code must be the first item in the code section.

    [Black] [Blue] [Cyan] [Green] [Magenta] [Red] [White] [Yellow]

    Hiding various values:

    To hide Use this code
    Zero values 0;–0;;@
    All values ;;; (three semicolons)
  • [Wayback] ECMA-376 – Ecma International where Ecma-376 4th Edition Part 1, Section 18.8.31 is supposed to have format strings (will dig into this later)

    Office Open XML file formats – This Standard defines Office Open XML’s vocabularies and document representation and packaging

    1. [Wayback] ECMA-376, Part 1
    2. Not sure where Part 2 is
    3. [Wayback] ECMA-376, Part 3
    4. [Wayback] ECMA-376, Part 4
    5. Not sure where Part 5 is

Below a few screenshots while experimenting.

The first one shows the formulas, the second one the content. The Date value cells shows the values when entered as shown; the Date formatted cells are all formatted with yyyy-mm-dd;@ formatting.

I still need to figure out why using a =TEXT function shows #VALUE! whereas using cell formatting just a bunch of ############### (15 times a #, not sure if that is always the same number).

–jeroen

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

Get Formatted Value of Cell in Excel – Stack Overflow

Posted by jpluimers on 2022/01/18

It’s odd, but facing a potentially lot shorter life expectancy, I need to prepare to become more expendable in personal life.

This means that I need to document or/and automate a lot of duties.

In this case, it is administrative work as custodian for my brother that is based on scripts, Excel sheets and manual steps.

In order to lessen these steps, I wanted to get the formatted value of certain Excel WorkSheet cells so I could concatenate them in other places.

It appears that neither the CONCATENATE function (nor the more recently introduced CONCAT function) nor  & concatenation operator just take the unformatted value of the cell and put that in as text.

Note that the TEXTJOIN function is basically a CONCATENATE function with an extra delimiter parameter, so it does not format text.

One format I needed was YYYY-MM-DD, basically the ISO-8601 date format.

The Excel format string for this is yyyy-mm-dd;@, and the corresponding formula to apply it on cell L5 is =TEXT(J5, "yyyy-mm-dd;@")

Based on [Wayback] Get Formatted Value of Cell in Excel – Stack Overflow:

Use the TEXT() function:
TEXT(value, format_text)
So if the value is 23.5 and you pass =TEXT(A1, "$0.00") it will return $23.50
Source: http://office.microsoft.com/en-us/excel-help/text-function-HP010062580.aspx

At a later stage, I will look into the actual format strings.

References:

–jeroen

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

Infusion pump and PCA (patient-controlled analgesia) calculation

Posted by jpluimers on 2021/11/09

This is a great PCA calculator: [Archive.is] CADD calculator 0.5BETA END USER Google Docs – Google Sheets

Via:

Related:

–jeroen

Read the rest of this entry »

Posted in Development, Excel, Google, GoogleDocs, GoogleSheets, Office, Office Automation, Office VBA, Power User, Scripting, Software Development | Leave a Comment »

How to count the number of days / workdays / weekends between two dates in Excel?

Posted by jpluimers on 2021/03/09

For my link archive as the Excel functions WORKDAY [WayBack] and NETWORKDAYS [WayBack] helped me finding the working days in a month a while ago: [WayBack] How to count the number of days / workdays / weekends between two dates in Excel?.

Note:

–jeroen

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

How to quickly add hours/minutes/second to date and time in Excel?

Posted by jpluimers on 2020/11/11

Excel date times are almost identical to OLE/COM automation date times (that used in many Windows applications).

Which means that dates are 1 unit apart, and:

  • Weeks = 7
  • Days = 1
  • Hours = 1/24
  • Minutes = 1/(24*60) or 1/1440
  • Seconds = 1/(24*60*60) or 1/86400

That way you can easily add one hour to a date/time value by adding 1/24.

Source: How to quickly add hours/minutes/second to date and time in Excel?

Related:

–jeroen

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

 
%d bloggers like this: