MoonPoint Support Logo

 


Shop Amazon Warehouse Deals - Deep Discounts on Open-box and Used ProductsAmazon Warehouse Deals



Advanced Search
June
Sun Mon Tue Wed Thu Fri Sat
         
25 26 27 28 29 30
2018
Months
Jun
Jul Aug Sep
Oct Nov Dec


Thu, Mar 01, 2018 10:44 pm

Determining the the elapsed time between two timestamps in Excel

If you have an Excel workbook containing two cells that contain a date and time and you want to know the time difference between them in days and hours, you can subtract one from the other and get the elapsed time between the two timestamps in days and hours by using a custom date and time format for the cell that will hold the results. E.g., suppose I have a spreadsheet with the following timestamps in columns A and B:

  A B C
1 Start Time End Time Elapsed Time
2 1/1/18 0:01 3/1/18 15:03  
3 2/6/18 15:18 2/7/18 18:07  
4 3/1/18 7:55 3/1/18 13:01  

The cells containing the date and time have the custom format m/d/yy h:mm.

[ More Info ]

[/software/office/excel] permanent link

Thu, Feb 15, 2018 10:22 pm

Enabling the Developer Tab in Excel for Mac 2016

To be able to view the Developer Tab in Microsoft Excel for Mac 2016, take the following steps:
  1. Click on Excel at the top, left-hand corner of the Excel window and the select Preferences.
  2. On the Excel Preferences window, click on View in the Authoring section.
  3. In the View window, click on the check box next to Developer tab, which you will see in the In Ribbon, Show section.
  4. You can close that window by clicking on the "x" in the red circle at the top, left-hand corner of the window. You should then see Developer as a selectable option to the right of Data, Review, and View on the menu bar at the top of the Excel window.
  5. If you click on the Developer tab, you should see options that include Visual Basic, Macros, Record Macro, Add-ins, Excel Add-ins, Button, Group Box, Combo Box, Label, Check Box, Scroll Bar, List Box, Option Button, and Spinner.

[ More Info ]

[/software/office/excel] permanent link

Tue, Jan 16, 2018 10:30 pm

Can't insert worksheet in Microsoft Excel for Mac 2016

When I tried to insert another worksheet in an Excel workbook today, I found that the option to insert a new blank worksheet was grayed out when I clicked on Insert and selected Sheet.

Excel 2016 - insert sheet grayed out

When I tried another means of adding a new sheet by clicking on the plus sign at the right side of the list of sheets in the worksheet to add the sheet, I saw an alert message stating "Workbook is protected and cannot be changed."

[ More Info ]

[/software/office] permanent link

Mon, Nov 20, 2017 10:22 pm

EMF image embedded in a PowerPoint file on OS X

Someone sent me a Microsoft PowerPoint presentation to review. The file I received was a .pptx file which I opened using the PowerPoint application in Microsoft Office 2016 for Mac on my MacBook Pro laptop running OS X El Capitan (10.11.6). When I viewed the presentation, I noticed one of the slides was blank. I sometimes receive Microsoft Excel workbooks which contain a worksheet that should normally contain network diagrams where the diagrams don't appear when viewed on my Mac laptop, but do appear when I open the file in Microsoft Excel on a system running the Microsoft Windows operating system. In such cases, I've found that since the .xlsx or .xlsm file format is just an XML-based container format akin to a zip file, I rename the files where the problem occurs to have a .zip rather than .xlsx or .xlsm extension, which then allows me to extract the files contained within the file - see Zipping and unzipping Excel xlsx files and Extracting embedded documents from an Excel .xlsm file. Then I use the OS X file command in a Terminal window to examine the .bin files in the xl/embeddings subdirectory that is produced when I extract the files and folders from the zip file. That utility tells me which of the .bin files represent embedded Microsoft Visio or PowerPoint files, so I can then give the Visio ones a .vsd extension rather than a .bin extension. I can then view the diagrams with the free VSD Viewer Pro application I have on the Mac. Since there are usually several .bin files in the directory, I created a Python script to determine the file type for all of the files in a directory at once.

[ More Info ]

[/software/office/powerpoint] permanent link

Mon, Sep 18, 2017 11:16 pm

Excel - This file is locked for editing

If you see an error message like the one below, which was produced by Microsoft Excel for Mac 2016 on a Mac OS X system, even though you don't have the file open currently, then you will need to delete the lock file, which should be in the same directory as the spreadsheet.

This file is locked for editing.

Locked by: John Doe
Filename: SGRS_2017.xlsm

You can open the file as read-only.

  

The lock file will have the same name as the workbook you were trying to open, but the file name will have ~$ prepended to it. To delete the file you will need to "escape" the meaning of the dollar sign by putting an escape character, i.e., a backslash character, immediately before the dollar sign. I.e., use ~\$ as shown below:

$ ls -alg **SGRS_2017.xlsm
-rw-rw-r--@ 1 ABC\Domain Users  761327 Sep 13 15:57 SGRS_2017.xlsm
-rw-rw-r--@ 1 ABC\Domain Users     171 Sep 18 22:46 ~$SGRS_2017.xlsm
$ rm ~$SGRS_2017.xlsm
rm: ~.xlsm: No such file or directory
$ rm ~\$SGRS_2017.xlsm
$

Once the lock file has been deleted, you should be able to open the file without the warning message that it is locked for editing.

[/software/office/excel] permanent link

Sat, Jul 29, 2017 10:05 pm

Chopping off the leftmost or rightmost character of a string in Excel

You can use the LEFT and RIGHT functions in the Microsoft Excel spreadsheet program along with the LEN (length) function to remove the leftmost or rightmost character from a text string. These functions also work in Google Sheets, LibreOffice Calc, which is the spreadsheet component of the LibreOffice software package, and Apache OpenOffice Calc, which is the spreadsheet program included in Apache OpenOffice, though in the Apache OpenOffice Calc program you need to substitute semicolons (;) for commas (,) in the formulas. E.g., in Apache OpenOffice Calc you would need to use =RIGHT(A5;LEN(A5) -1) , instead of =RIGHT(A5,LEN(A5) -1) as you would in the other programs.

Removing the leftmost character

The syntax for the RIGHT function is RIGHT(text,[numchars]). If you don't include numchars, i.e., you use RIGHT(text) then the value returned is the rightmost character in the string. E.g., if cell A1 has 1ABC in it, then =RIGHT(A1) returns C. But suppose, instead, you want to remove the leftmost charaacter from a string. You can use the RIGHT function to do so. E.g., suppose I have a column of values, e.g.:

  AB
1 1ABC 
2 2DEF 
3 3GHI 
4 4JKL 
5 5MNO 

If I want to remove the number at the beginning of each text string and put the shortened strings in column B, I could, since the strings are all 4 characters long, use =RIGHT(A1,3) in column B1 and then copy the formula down through the other cells in column B by clicking in cell B1 and holding down the leftmost mouse button and dragging downwards through the other cells in column B and then hitting Ctrl-D.

But suppose the strings vary in length. I.e., suppose I have a worksheet containing the following strings in column A:

  AB
1 1ABC 
2 2DEFG 
3 3HI 
4 4JKL 
5 5MNOP 

[ More Info ]

[/software/office/excel] permanent link

Sat, Jul 22, 2017 5:53 pm

Counting entries in an Excel spreadsheet by AM or PM

Microsoft Excel supports formatting timestamps in a workbook as a date followed by a time with an AM or PM value, i.e., the times are in 12-hour clock format where noon is 12:00 PM and midnight is 12:00 AM. You can select that format for a cell or column in a worksheet by highlighting the cell(s) or column (a column can be selected by clicking on the letter at the top of the column) and then clicking on Format then Cells and then clicking on Date and selecting a type of "3/14/15 1:30 PM."

If you wanted to extract just the hour from the timestamp, you can use the HOUR function, e.g. =HOUR(A1) to extract the hour value from cell A1. The hour will be displayed in 24-hour clock format, aka military time, i.e., 7:00 AM is 7 while 7:00 PM is 12 plus 7, i.e., 19; 12:00 AM (midnight) is 0 and 12:00 PM (noon) is 12.

[ More Info ]

[/software/office/excel] permanent link

Sun, Jun 04, 2017 11:21 pm

ISURL

If you wish to know whether a cell holds a Uniform Resource Locator (URL), such as http://example.com, in Google Sheets you can use the ISURL function. E.g., if I wanted to know whether cell A5 contains a URL, elsewhere in the spreadsheet I could use the formula =ISURL(A5). The value returned will be either TRUE or FALSE. Note: this will return the Boolean value TRUE or FALSE only if the text in the cell is a URL. If, instead, I have =hyperlink("http://superuser.com","Super User"), i.e., I have text in the cell that is hyperlinked, the value will be FALSE, because, in the example, the text that appears in the cell will be Super User.

This function is not available in Microsoft Excel, at least as of Excel 2013 for Windows and Microsoft Excel for Mac 2016 (version 15.29), which is part of Microsoft Office 2016 for OS X and macOS systems. Nor is it available for Apache OpenOffice Calc, at least as of version 4.1.1. If you try using ISURL as a formula in those applications, you will see #NAME? appear in the cell where you place the formula, since its usage is an incompatibility between those versions and Google Sheets.

The ISURL formula will return TRUE for other URLs besides HTTP or HTTPS ones. E.g., FTP and mailto URLs will also result in a value of TRUE. E.g., if a cell contains any of the following URLs, an ISURL formula that checks the content of the cell will return TRUE.

http://example.com
https://www.example.com
ftp://ftp.microsoft.com
mailto:someone@example.com

[/software/office/excel] permanent link

Wed, Apr 19, 2017 10:11 pm

Excel 2016 - Workbook Protected

I wanted to add another sheet to an Excel workbook, but when I clicked on the "+" to add the additional worksheet, I saw the message "Workbook is protected and cannot be changed."

Excel 2016 - workbook protected

I didn't know what the password might be because I had never set a password for the workbook, which I had created in a prior version of Excel; I'm using Microsoft Office 2016 on a MacBook Pro running OS X El Capitan (10.11.6) at the moment.

[ More Info ]

[/software/office/excel] permanent link

Sun, Apr 09, 2017 4:51 pm

Adding a checkbox in Microsoft Excel

To add a checkbox (check box, tickbox, tick box) column in Microsoft Excel, you will need to first enable the developer ribbon in Excel, if it is not already enabled. To do so, take the following steps in Excel (the steps were written for Excel 2010 and 2013, but may be similar for other versions):

Click on File and select Options.

Excel - File - 
Options

[ More Info ]

[/software/office/excel] permanent link

Once You Know, You Newegg AliExpress by Alibaba.com

Shop Amazon Local - Subscribe to Deals in Your Neighborhood

Valid HTML 4.01 Transitional

Privacy Policy   Contact

Blosxom logo