Archive for the ‘Office’ Category
Posted by jpluimers on 2023/01/25
Given cell A1 is a valid date, I wanted to know the day of that date in that year.
My solution is =A1-DATE(YEAR(A1)-1,12,13)
I disliked the solution in [Wayback/Archive] Day of the Year in Microsoft Excel and [Archive] Day of the Year in Excel (In Easy Steps) (excluded from the WayBack machine), as it is unclear where the + comes from in their solution =A1-DATE(YEAR(A1),1,1)+1
So, here goes my solution, with explanation:
=YEAR(A1) is the year of A1=YEAR(A1)-1 is year before A1=DATE(YEAR(A1)-1,12,13) is the last day of year before A1=A1-DATE(YEAR(A1)-1,12,13) is the day of the year of A1
The last step works because subtracting two dates in Excel returns the number of days between those two dates (in a similar way, you can add a number to a date to get a new date number days in the future; similarly you can add time portions as fractions of a day).
The linked solution uses:
=YEAR(A1) is the year of A1=DATE(YEAR(A1),1,1) is the first day of the year of A1=DATE(YEAR(A1),1,1)-1 is the last day of the year before A1=A1-(DATE(YEAR(A1),1,1)-1) is the day of the year of A1=A1-DATE(YEAR(A1),1,1)+1 is a simplification of the day of the the year of A1
[Wayback/Archive] excel days from start of year – Google Search
–jeroen
Posted in Excel, Office, Power User | Leave a Comment »
Posted by jpluimers on 2022/12/15
I had trouble creating a Microsoft Account (so I could hand out on-line Office Licenses to users) where each time after solving the image or audio puzzle, it would shortly display a success, then loop back to the puzzle.
So I wrote a [Wayback/Archive] Thread by @jpluimers on Thread Reader App as I got stuck even before trying to add a Microsoft Account on any on m’y devices Windows 11 or Windows 10. It started with
[Wayback/Archive] Hi @MicrosoftHelps, I have a different problem. When creating an account using Edge on Windows 10 (latest version of both; no plugins; Microsoft Defender in default settings), solving the puzzle loops back to “Please solve the puzzle so we know you’re not a robot.”
The odd thing is that it did not work in either Edge or Chrome (both based on Chromium which uses the Blink browser engine) even in the anonymous/private browsing mode (InPrivate for Edge or Incognito Mode for Chrome).
Even Firefox had trouble, despite it being based on a totally different Gecko browser engine: after solving the puzzle it would continue but instead of displaying success, it showed an error page (the error page also occurred in the Firefox Private Browsing mode):
Read the rest of this entry »
Posted in Office, Power User, Windows, Windows 10, Windows 11 | Leave a Comment »
Posted by jpluimers on 2022/03/31
Usually, I work in a single worsheet and workbook at a time.
Sometimes I use multiple worksheets or workbooks, and I never knew about the below feature.
The really cool aspect which is not in the below documentation: if you re-open a worksheet, it remembers the views and positions!
Learned a new thing and discovered a new thing (:
[WayBack] View multiple panes, sheets, or workbooks – Excel for Mac:
In Excel for Mac, you can split a sheet in to panes, view multiple sheets in one workbook, or view multiple workbooks at a time.
View multiple sheets in one workbook
- Open the workbook that you want to view.
- On the Window menu, click New Window.
- Switch to the new window, and then click the tab for the sheet that you want to view.Repeat steps 2 and 3 for each sheet that you want to view.
- On the Window menu, click Arrange.
- Do one of the following:
| To arrange windows |
So that they appear like this |
Click |
| As equally sized, tiled squares |
 |
Tiled |
| Horizontally from top to bottom |
 |
Horizontal |
| Vertically from right to left |
 |
Vertical |
| In an overlapping cascade from top to bottom |
 |
Cascade |
- Select the Windows of active workbook check box.
View multiple workbooks
- Open all the workbooks that you want to view.
- On the Window menu, click Arrange.
- Do one of the following:
| To arrange windows |
So that they appear like this |
Click |
| As equally sized, tiled squares |
|
Tiled |
| Horizontally from top to bottom |
|
Horizontal |
| Vertically from right to left |
|
Vertical |
| In an overlapping cascade from top to bottom |
|
Cascade |
–jeroen
Posted in Excel, Office, Office 2011 for Mac, Power User | Leave a Comment »
Posted by jpluimers on 2022/03/01
Sometimes, you figure out Excel functionality you have never needed before, but has been there for decades.
A while ago, I had a very complex with formulas referencing full columns back and forth when suddenly I got into something strange: when reloading the spreadsheet, values would not appear because of recursion errors. Before saving this was fine, so it was hard to track back where I want wrong.
So I was happy to find out that Excel has two cool features for this:
- Trace Precedents
- Trace Dependents
Heck, looking at the icons I had a feel these features had been there for a long time. Boy, was I surprised to find them in [Wayback] Excel 2000 – Student Edition – Complete (a great book by the way), as you can see in this picture:
Excel 2000 – Student Edition – Complete – Trace precedents, dependents, error
As others can explain this feature so much better than I can, here are some links:
–jeroen
Posted in Excel, Office, Office 2000, Office 2003, Office 2007, Office 2010, Office 2011 for Mac, Office 2013, Office 2016, Power User | Leave a Comment »
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:
- 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
- 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).
- [Wayback] Archive.is: Creating international number formats – Excel … HA001034635.aspx
- [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
- positive numbers,
- negative numbers,
- zero values, and
- text,
in that order. The sections of code must be separated by semicolons (;).
The following example shows the four types of format code sections.
Format for positive numbers
Format for negative numbers
Format for zeros
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
- [Wayback] ECMA-376, Part 1
- Not sure where Part 2 is
- [Wayback] ECMA-376, Part 3
- [Wayback] ECMA-376, Part 4
- 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 »
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 »
Posted by jpluimers on 2021/12/15
I always forget that, when moving a folder, instead of finding all references to that folder and fixing them, you can create an NTFS symlink from the old location to the new one.
[Wayback] how to move MSOCACHE folder from C-drive to D-drive ?? – Microsoft Community (thanks [Wayback] tgunda numbering and casing updates mine):
There are too much entries in the registry to correct them manually one by one.
An easier and quicker solution is to copy the full MSOCache folder to a new place and to make a soft link to it:
- Create a new folder, e.g.
F:\MSOCache
- Copy everything from
C:\MSOCache to the new one.
- Rename the old folder
C:\xMSOCache (Don’t delete it, just in case).
- Open a command prompt window in administrator mode.
- Write:
mklink /d c:\MSOCache f:\MSOCache
Now there is an MSOCache link at C, pointing to the new place.
If everything is OK, you can delete C:\xMSOCache
This can be very handy when moving around large software development installations, circumventing a full uninstall/install sequence loosing lots of configuration settings.
–jeroen
Posted in Development, LifeHacker, Office, Power User, Software Development, Windows, Windows Development | Leave a Comment »
Posted by jpluimers on 2021/11/09
Posted in Development, Excel, Google, GoogleDocs, GoogleSheets, Office, Office Automation, Office VBA, Power User, Scripting, Software Development | Leave a Comment »
The Wiert Corner - irregular stream of stuff 