Sunday, September 30, 2018

PerformancePoint Dashboard Designer Help

PerformancePoint Dashboard Designer Help

We have detailed information available on TechNet about how to create, edit, and publish dashboards by using Dashboard Designer. Please see Create Dashboards by using PerformancePoint Services (SharePoint Server 2013) on TechNet.

Attach files and graphics to the records in your database

Attach files and graphics to the records in your database

You use the Attachment feature in Access to add one or more files  — documents, presentations, images, and so on — to the records in your database. This article provides the background information and steps needed to configure a database to use attachments, and to attach and manage data.

Note: The functionality explained in this article doesn't apply to Access web apps.

In this article

Why should I use attachments?

Add an attachment field to a table

Attach files directly to tables

Use attachments with forms and reports

Save attached files to other locations

Remove attached files

Use attachments without a mouse or other pointing device

Attachments reference

Why should I use attachments?

You can use attachments to store several files in a single field, and you can even store multiple types of files in that field. For example, let's say you own a job contacts database. You can now attach one or more resumes to the record for each contact, plus a photo of each contact.

Attachments also store data more efficiently. Earlier versions of Access used a technology called Object Linking and Embedding (OLE) to store images and documents. By default, OLE created a bitmap equivalent of the image or document. Those bitmap files could become quite large — as much as 10 times larger than the original file. When you viewed an image or a document from your database, OLE showed you the bitmap image, not the original file. By using attachments, you open documents and other non-image files in their parent programs, so from within Access, you can search and edit those files.

In addition, OLE requires programs called OLE servers to function. For example, if you store JPEG image files in an Access database, every computer that runs that database requires another program that is registered as an OLE server for JPEG images. In contrast, Access stores the attached files in their native formats with no supporting images, and you do not need to install additional software in order to view the images from within your database.

Attachments and database design rules

By default, each field in a relational database contains only one piece of data. For example, if an address field contained more than one address, finding addresses would be difficult, if not impossible. So at first glance, attachments seem to break the rules of database design because you can attach more than one file — one piece of data — to a field. However, attachments do not break any design rules, because as you attach files to a record, Access creates one or more system tables and uses those tables behind the scenes to normalize your data. You cannot view or work with those tables.

For information about viewing other system tables, see the article Guide to the Navigation Pane. For information about database design, see the article Database design basics.

Ways that you can use attached files

Remember these guidelines as you work with attached files:

  • You can attach files only to databases that you create in Access and that use the .accdb file format. You cannot share attachments between a Access (.accdb) database and a database in the earlier (.mdb) file format.

  • You must create a field in a table and set that field to the Attachments data type. After you set the data type to Attachments, you cannot change it.

  • You can store multiple files in a single record. For example, you can store images, and files created with word processing and spreadsheet programs.

  • You can attach a maximum of two gigabytes of data (the maximum size for an Access database). Individual files cannot exceed 256 megabytes in size.

  • You use the Attachments dialog box to add, edit, and manage attachments. You can open the dialog box directly from the attachment field in a table by double-clicking the field. If you want to manage attachments from a form or view attachments from a report, you add the attachment control to the form or report and then bind the control to the underlying attachment table field.

  • By default, the attachment control renders images and displays the program icon that corresponds to other file types. For example, let's say you have a photo, a resume, and a Visio drawing attached to a record. When you scroll through the attachments, the control renders the image and displays the program icons for the document and the drawing.

  • When you open the Attachments dialog box from a table or a form, you can add, remove, edit, and save attached files. When you open the Attachments dialog box from a report, you can save the attached files only to another location.

  • Access will compress your attached files unless those files are compressed natively. For example, JPEG files are compressed by the graphics program that created them, so Access does not compress them.

  • If the program that was used to create the attached file is installed on your computer, you can open and edit the attached files in that program.

  • You can save the attached files to locations on your hard disk drive or on the network. Then you can edit the attachments and make sure that you like your changes before you save those changes back to your database.

  • You can manipulate attachments programmatically.

The steps in the following sections explain how to add and manage attachments.

Top of Page

Add an attachment field to a table

To use attachments in Access, you must first add an attachment field to at least one of the tables in your database. Access provides two ways to add an attachment field to a table. You can add the field in Datasheet view, or you can add it in Design view. The steps in this section explain how to use both techniques.

Add an attachment field in Datasheet view

  1. With the table open in Datasheet view, click the first available blank column. To find a blank column, look for the words Add New Field in the column header.

  2. On the Datasheet tab, in the Data Type & Formatting group, click the down arrow next to Data Type, and then click Attachment.

    Access sets the data type for the field to Attachment, and places an icon in the header row of the field. The following figure shows a new Attachment field. Note the paperclip icon in the header row of the field. By default, you cannot enter text in the header row of Attachment fields.

    A new table field set to the Attachment data type

  3. Save your changes.

    Remember that you cannot convert the new field to another data type, but you can delete the field if you think you made an error.

Add an attachment field in Design view

  1. In the Navigation Pane, right-click the table that you want to change and click Design View Button image on the shortcut menu.

  2. In the Field Name column, select a blank row and enter a name for your Attachment field.

  3. In the same row, under Data Type, click Attachment.

  4. Save your changes.

    Remember that you cannot convert the new field to another data type, but you can delete the field if you think you made an error.

  5. On the Design tab, in the Views group, click the arrow beneath the View button, and then click Datasheet View to open the table for use.

    Access Ribbon Image

    -or-

    Right-click the document tab for the table and click Datasheet View on the shortcut menu.

    -or-

    In the Navigation Pane, right-click the table and click Open on the shortcut menu.

  6. Go on to the next steps.

Top of Page

Attach files directly to tables

After you add an attachment field to a table, you can attach the files to the records in that table without having to create a data-entry form. You can also view attachments without using a form, but remember that when you view attachments directly from tables, you must do so in the programs that were used to create the files, or in a program that supports that type of file. For example, if you open a Word document that is attached to a table, Word also starts and you view the document from within Word, not Access. If Word is not installed on your computer, a dialog box appears and asks you to select a program for viewing the file.

Add an attachment to a table

  1. With the table that contains your attachment field open in Datasheet view, double-click the attachment field.

    The Attachments dialog box appears. The following figure shows the dialog box.

    The Attachments dialog box

  2. Click Add. The Choose File dialog box appears.

  3. Use the Look in list to navigate to the file or files that you want to attach to the record, select the file or files, and then click Open.

    You can select multiple files of any supported data type. For a list of supported data types, see Attachments reference later in this article.

  4. In the Attachments dialog box, click OK to add the files to your table.

    Access adds the files to the field and increments the number that indicates attachments accordingly. The following figure shows a field with two images attached:

    An Attachment field with two pieces of data

  5. Repeat these steps as needed to add files to the current field or other fields in your table.

Open the attached files from a table

  1. With the table open in Datasheet view, double-click the cell in the attachment field.

  2. In the Attachments dialog box, double-click the file that you want to open.

    –or–

    Select the file and click Open.

    The program associated with the file starts and opens the attached file — Excel files open in Excel, and so on.

    Some image files may open in Microsoft Windows Picture. If you want to do more than view the image, you can right-click the image and then click Edit. This starts the program that was used to create the file, if you have that program installed on your computer.

Save changes to an attached file

  1. As needed, use the parent program to edit your file.

  2. Save any changes to the file and close the parent program. Keep in mind that whenever you change an attached file, your changes are saved to the temporary Internet folder on your hard disk drive. See the notes at the end of this section for more information about that folder.

  3. To save your changes permanently, return to Access and, in the Attachments dialog box, click OK.

    A message similar to the following appears:

    A prompt to save the changes made to an attachment

  4. Click Yes to save your changes.

    Notes: 

    • When you open an attached file in its parent program for viewing or editing, Access places a temporary copy of the file in a temporary folder. If you change the file and save your changes from within the parent program, Access saves your changes to the temporary copy. When you return to Access and click OK to close the Attachments dialog box, Access prompts you to save the attached file again. Click Yes to write the changed file to your database, or click No to leave the file untouched in your database.

    • To find the location of your temporary file folder, follow these steps:

      1. Start Windows Internet Explorer.

      2. On the Tools menu, click Internet Options.

      3. On the General tab, click Settings.

        The Settings dialog box appears. The folder path appears in the Temporary Internet Files folder section.

Top of Page

Use attachments with forms and reports

When you need to use attachments with a form or report, you use the attachment control. The control renders image files automatically as you move among the records in a database. If you attach other types of files, such as documents or drawings, the attachment control displays the icon that corresponds to the file type. For example, you see the PowerPoint icon when you attach a presentation, and so on. The control also allows you to browse any attached files and open the Attachments dialog box. If you open the dialog box from a form, you can add, remove, edit, and save attachments. If you open the dialog box from a report, you can only save attachments to your hard disk drive or a network location, because reports are read-only by design.

The steps in the following sections explain how to add an image control to a form or report, and how to scroll through records, attach files, and view your attachments. Remember that you can scroll through attachments only when a given record contains more than one attachment.

Add the attachment control to a form or report

The steps in this section explain how to add the attachment control to a form or report, and then bind the control to an Attachment field in an underlying table. You follow the same steps when adding the attachment control to a form or a report. Before you start, remember that at least one of the tables in your database must have an attachment field. For information on adding an attachment field, see Add an attachment field to a table earlier in this article.

Because form and report design can be complex processes, the steps in these sections assume that you already have a database with at least one table and one form or report. For information on creating tables, forms, or reports, see the following articles:

Add the attachment control

  1. In the Navigation Pane, right-click the form or report that you want to change and click Design View on the shortcut menu.

  2. On the Design tab, in the Tools group, click Add Existing Fields.

    The Field List pane appears and lists the fields in the table that provide the data for the form or report. The list denotes an attachment field by making it expandable (that is, you can click the plus or minus sign next to the field).

    The following figure shows a typical attachment field in the Field List pane.

    An Attachment field in the Field List task pane

  3. Drag the entire attachment field from the list to your form — the parent and child items — and drop it in the location that you want on your form.

    Access places an attachment control on your form and binds the control to the table field for you.

  4. As needed, right-click the control and click Properties to display the property sheet for the control. Set or change the control properties to match the rest of your form or report.

  5. Save your changes, and then right-click the document tab and click either Form View or Report View to open the form or report for viewing.

    If the underlying field contains image files, the control renders those files. If the field contains another type of file, such as a Word document or PowerPoint presentation, the control displays the appropriate icon for that file type.

Manage attachments by using a form

After you add an attachment control to a form, you can add, edit, remove, and save attached files directly from that form. When a record contains multiple attachments, you can also scroll through the attached files, which you cannot do when working with a table.

Note: The person who designed the form may have made the form read-only. If that is the case, you can use the Attachments dialog box only to save attached files to your hard disk drive or a location on your network.

Attach a file

  1. Open the form that displays your attachments, and locate the record to which you want to attach a file.

  2. Select the attachment control — the control that is bound to the Attachment field.

    The Mini toolbar appears: The Attachment Mini toolbar

    Note: If you added the attachment control to the datasheet section of a split form, the Mini toolbar does not appear. For more information about split forms, see the article Create a split form.

  3. Click the View Attachments button (the paperclip icon) to open the Attachments dialog box.

  4. In the dialog box, click Add.

    The Choose File dialog box appears.

  5. Use the Look in list to navigate to the file that you want to attach, and then click Open.

  6. Repeat steps 4 and 5 as needed to attach more files.

Scroll through the attached files

Note: The steps in this section apply to forms and reports.

  1. Open the form or report that displays your attachments.

  2. Navigate to the record that contains the attached files.

  3. Click the image control that displays the attached files.

    The Mini toolbar appears.

  4. Click the Back (left) or Forward (right) arrows to scroll through the attached files. If you want to know the names of the files, click the View Attachments button to open the Attachments dialog box. The names of attached files appear in the Attachments list.

Top of Page

Save attached files to other locations

The steps in this section apply to tables, forms, and reports. You can save either one or all of the files that are attached to a given record to locations on your hard disk drive or network. Remember that when you choose to save all files, you cannot choose to save some of the files — you must save them all. To selectively save files, you need to do so one at a time.

  • Open the table, form, or report that contains your attachments, and then open the Attachments dialog box.

Open the Attachments dialog box from a table

  • Open the table in Datasheet view, and then double-click the attachment field that contains the attachment you want to save.

Open the Attachments dialog box from a form or report

  1. Open the form or report that displays your attachments.

  2. Navigate to the record that contains the attached files.

  3. Click the image control that displays the attached files.

    The Mini toolbar appears.

  4. Click the View Attachment button.

Save a single attachment

  1. In the Attachments dialog box, click Save As.

    The Save Attachment dialog box appears.

  2. Use the Save in list to navigate to the new location for your file, and then click Save.

Save all attachments

  1. In the Attachments dialog box, click Save All.

    The Save Attachments dialog box appears.

  2. Use the Look in list to navigate to the new location for your files, and then click Save.

Top of Page

Remove attached files

The steps in this section apply to tables and forms.

Remove an attachment

  1. Double-click the attachment field in your table to open the Attachments dialog box.

    –or–

    In your form (in either Layout view or Form view), navigate to the record that contains the attachment you want to remove, and click the View Attachment button on the Mini toolbar to open the dialog box.

  2. In the Attachments dialog box, select the file that you want to delete and click Remove.

Top of Page

Use attachments without a mouse or other pointing device

The following section explains how to use your keyboard to place focus in the Navigation Pane and open the table, form, or report that contains attached files. The steps also explain how to browse attached files and open the Attachments dialog box.

Open a table, form, or report from the Navigation Pane

  1. Press F11.

    Note: If the Navigation Pane is closed, pressing F11 opens it and places the focus in the pane. If the pane is open, pressing F11 closes it. You must press F11 again to open the pane and shift focus to it.

  2. Use the UP ARROW and DOWN ARROW keys to select the table, form, or report that you want to open.

  3. Press ENTER to open the selected object.

    If you open a table, Access places the cursor in the first field in the table. If you open a form or report, Access places the focus in the first field.

View attachments from tables

  1. As needed, use the arrow keys to move the cursor to the attachment field that you want.

  2. Press the SPACEBAR.

    The Attachments dialog box appears.

  3. Press the TAB key to move among the buttons in the dialog box and to move from the buttons to the list of attached files under Attachments.

    Note: Records may contain more than one attachment. If you need to select an attachment from a list of two or more files, press the TAB key to move to the file list, and then use the arrow keys to select the file that you want. Next, press the TAB key to return to the buttons and select the action that you want.

  4. When you select the file and button that you want, press ENTER.

  5. When you finish, press the key TAB, or use the UP ARROW and DOWN ARROW keys to select OK, and then press ENTER.

Scroll through attachments from a form or report

These steps apply only if you have a Microsoft Natural Keyboard and when a record contains more than one attachment.

  1. As needed, press the TAB key to move the focus to the attachment control. By default, Access highlights the control and the label associated with the control, if the label exists.

  2. Press the Application key.

    A shortcut menu appears.

  3. Press the TAB key, or use the arrow keys to select either Forward or Back, and then press ENTER.

  4. Repeat step 2 as needed to scroll among the attached files.

Open the Attachments dialog box from a form or report

These steps apply only if you have a Microsoft Natural Keyboard.

  1. As needed, press the TAB key to move the focus to the attachment control. By default, Access highlights the control and the label associated with the control, if the label exists.

  2. Press the Application key.

    A shortcut menu appears.

  3. Press the TAB key, or use the arrow keys to select View Attachments, and then press ENTER.

    The Attachments dialog box appears.

  4. Press the TAB key to move among the buttons in the dialog box and to move from the buttons to the list of attached files under Attachments (Double-click to edit).

    Records may contain more than one attachment. If you need to select an attachment from a list of two or more files, press the TAB key to move to the file list, and then use the arrow keys to select the file that you want. Next, press the TAB key to return to the buttons and select the action that you want.

  5. When you select the file and button that you want, press ENTER.

  6. When you finish, press the TAB key or use the arrow keys to select OK, and then press ENTER.

Top of Page

Attachments reference

The following sections provide reference information about attachments, including the image and document file formats that attachments support, file-naming conventions, and some information about attaching files to records programmatically.

Supported image file formats

Access supports the following graphic file formats natively, meaning the attachment control renders them without the need for additional software.

BMP (Windows Bitmap)

RLE (Run Length Encoded Bitmap)

DIB (Device Independent Bitmap)

GIF (Graphics Interchange Format)

JPEG, JPG, JPE (Joint Photographic Experts Group)

EXIF (Exchangeable File Format)

PNG (Portable Network Graphics)

TIFF, TIF (Tagged Image File Format)

ICON, ICO (Icon)

WMF (Windows Metafile)

EMF (Enhanced Metafile)

Supported formats for documents and other files

As a rule, you can attach any file that was created with one of the Microsoft Office programs. You can also attach log files (.log), text files (.text, .txt), and compressed .zip files.

File-naming conventions

The names of your attached files can contain any Unicode character supported by the NTFS file system used in Microsoft Windows NT (NTFS). In addition, file names must conform to these guidelines:

  • Names must not exceed 255 characters, including the file name extensions.

  • Names cannot contain the following characters: question marks (?), quotation marks ("), forward or backward slashes (/ \), opening or closing brackets (< >), asterisks (*), vertical bars or pipes (|), colons (:), or paragraph marks (¶).

Types of files that Access compresses

When you attach any of the following file types to a database, Access compresses them if they are not already compressed natively.

File Extension

Compressed?

Reason

.jpg, .jpeg

No

Already compressed

.gif

No

Already compressed

.png

No

Already compressed

.tif, .tiff

Yes

.exif

Yes

.bmp

Yes

.emf

Yes

.wmf

Yes

.ico

Yes

.zip

No

Already compressed

.cab

No

Already compressed

.docx

No

Already compressed

.xlsx

No

Already compressed

.xlsb

No

Already compressed

.pptx

No

Already compressed

Blocked file formats

Access blocks the following types of attached files. At this time, you cannot unblock any of the file types listed here.

.ade

.ins

.mda

.scr

.adp

.isp

.mdb

.sct

.app

.its

.mde

.shb

.asp

.js

.mdt

.shs

.bas

.jse

.mdw

.tmp

.bat

.ksh

.mdz

.url

.cer

.lnk

.msc

.vb

.chm

.mad

.msi

.vbe

.cmd

.maf

.msp

.vbs

.com

.mag

.mst

.vsmacros

.cpl

.mam

.ops

.vss

.crt

.maq

.pcd

.vst

.csh

.mar

.pif

.vsw

.exe

.mas

.prf

.ws

.fxp

.mat

.prg

.wsc

.hlp

.mau

.pst

.wsf

.hta

.mav

.reg

.wsh

.inf

.maw

.scf

Attach files to records programmatically

Access exposes an object model and programming interfaces for attaching files to records programmatically by using Visual Basic for Applications (VBA) code. For information about attaching files programmatically, see the articles "LoadFromFile" and "SaveToFile," on the Microsoft Developer Network — https://msdn.microsoft.com.

Top of Page

End of support for Office 2007

End of support for Office 2007

Support for Office 2007 ended on October 10, 2017. All of your Office 2007 apps will continue to function. However, you could expose yourself to serious and potentially harmful security risks. Upgrade to a newer version of Office so you can stay up to date with all the latest features, patches, and security updates.

Link to buy or try Office

Save a plan to PDF or XPS

Save a plan to PDF or XPS

The PDF and XPS file formats are popular ways to save and view a project plan for other people to see, without the other people needing Project installed on their computers.

  1. Click the File tab, and then click Share.

  2. Click Create PDF/XPS Document, and then on the right, click Create a PDF/XPS.

  3. In the Browse dialog box, select a name and location for the document.

  4. In the Save as type list, select either PDF files or XPS files, and then click OK.

More about PDF and XPS documents

Project 2010 supports exporting your file to the following formats:

  • Portable Document Format (PDF)     PDF is a fixed-layout electronic file format that preserves document formatting and enables file sharing. The PDF format ensures that when the file is viewed online or printed, it retains exactly the format that you intended, and that data in the file cannot be easily changed. The PDF format is also useful for documents that will be reproduced by using commercial printing methods.

  • XML Paper Specification (XPS)     XPS is an electronic file format that preserves document formatting and enables file sharing. The XPS format ensures that when the file is viewed online or printed, it retains exactly the format that you intended, and that data in the file cannot be easily changed.

Note: After you print a plan to PDF, you cannot use Project to make changes directly to the PDF or XPS document. You must make changes to the original plan by using Project and then save the file as PDF or XPS again.

Top of Page

Set up email on other Internet-capable phones and tablets

Set up email on other Internet-capable phones and tablets

You can set up email on a wide variety of Internet-capable mobile phones and tablets using POP or IMAP. However, since most mobile phones and devices support Exchange ActiveSync, we recommend that you connect to your account using Exchange ActiveSync (also known as connecting through an Exchange account). When you connect to your account using Exchange ActiveSync, you can use email, calendar, contacts, and other collaboration features that are available with your account. If you connect using POP or IMAP, you can only use email features.

Set up POP or IMAP email on an Internet-capable phone

Start the email program on your phone and choose the option to add an account. If you need help finding the email app, use the documentation that was included with your phone or tablet.

Use the following information to help you set up your email if you use Office 365. If you're not using Office 365, see Find your server settings later in this article.

  • Email address: This is your full email address (for example, tony@contoso.com).

    Email address: This is your full email address (for example, tony@contoso.cn).

  • User name: Your user name is your full email address (for example, tony@contoso.com).

    User name: Your user name is your full email address (for example, tony@contoso.cn).

  • Password: This is the password for your email account.

  • Server settings: These are your incoming and outgoing server settings. Choose either POP3 or IMAP4.

    Incoming server settings:

    POP3 Server name: outlook.office365.com

    POP3 Server name: partner.outlook.cn

    IMAP4 Server name: outlook.office365.com

    IMAP4 Server name: partner.outlook.cn

    Port: 995

    Encryption: SSL

    Outgoing server settings:

    SMTP Server name: smtp.office365.com

    SMTP Server name: smtp.office365.cn

    Port: 587

    Encryption: TLS

  • Authentication settings: If the program allows you to set up advanced settings use the following settings.

    • Select the outgoing server requires authentication option.

    • Select the setting that lets you use the same user name and password for sending email option.

    • Don't select the setting for Secure Password Authentication.

Find your server settings

If you're connecting to an Exchange mailbox and not using Office 365 email, or if you aren't sure if you're using Office 365 email, do the following to look up your settings.

  1. Sign in to your account using Outlook Web App.

  2. In Outlook Web App, on the toolbar, select Settings Settings icon > Mail > POP and IMAP.

  3. The POP3, IMAP4, and SMTP server name and other settings you may need to enter are listed on the Settings for POP and IMAP Access page under POP setting, IMAP setting   , and SMTP setting.

What else do I need to know?

  • If you see Not available under POP setting, IMAP setting   , and SMTP setting, you may need to contact the person that manages you email account to determine your Exchange ActiveSync Server name.

  • Setting up an email account on your mobile phone requires that you have a data plan with your mobile phone carrier.

  • Secure Sockets Layer (SSL) and Transport Layer Security (TLS) are methods to help secure communications between your computer and the email server. Some email programs refer to SSL or TLS as "encryption." In most email programs, you need to open an "Advanced" setting or tab to set SSL for POP and IMAP connections and TLS for SMTP connections.

  • Your email program may automatically try to use port 110 if you're using POP or port 143 if you're using IMAP. If your email program shows port 110 or 143, be sure to select SSL encryption. Then, enter a port number of 995 to replace the default port number (110) if you're using POP or a port number of 993 to replace the default port number (143) if you're using IMAP.

  • Your email program may automatically try to use server port 25 for SMTP. If your email program shows port 25 for SMTP, be sure to select TLS encryption. Then, enter 587 to replace the default port number (25).

  • If your email account is the type that requires registration, you must register it the first time you sign in to Outlook Web App. Connecting to your e-mail account through a mobile phone will fail if you haven't registered your account through Outlook Web App. After you sign in to your account, sign out. Then try to connect using your mobile phone.

  • If you connect to your account using a POP or IMAP connection, you will only be able to use and synchronize your email by using a wireless connection. If you can connect using an Exchange ActiveSync account, you can synchronize email, contacts, and calendar.

Subtract dates

Subtract dates

You can quickly find the difference between two dates, control the format of the result, and extend the calculation to a list of dates. For example, you can quickly subtract one date from another date, or calculate the duration of items on a project schedule.

Use the provided sample data and the following procedures to learn how to calculate dates in a project schedule.

Copy the sample data

  1. Copy the following sample data to cell A1 on a blank sheet.

    Task

    Start date

    End date

    Duration

    Create Project Plan

    6/8/2011

    9/20/11

    Phase 1

    9/21/11

    6/2/2012

    Phase 2

    6/3/2012

    12/10/2014

    Phase 3

    12/11/2014

    4/20/2017

  2. Press COMMAND + C .

  3. In the sheet, select cell A1, and then press COMMAND + V .

Calculate the difference in days

Calculate the number of days between two dates

  1. Select cell D2, which is the first blank cell in the Duration column.

  2. Type =C2-B2, and then press RETURN .

    Excel displays the result as the number of days between the two dates (104).

  3. Select cell D2.

  4. To copy the formula into the remaining rows, drag the fill handle   Fill handle .

    Excel adjusts the cell references automatically to include the correct values for each row.

Calculate the number of workdays between two dates

  1. Select cell D2, which is the first blank cell in the Duration column.

    If the cell is not blank, on the Edit menu, point to Clear, and then click All.

  2. Do one of the following:

    • In Excel for Mac 2011, on the Formulas tab, under Function, click Formula Builder.

      Formulas tab, Function group

    • In Excel 2016 for Mac, on the Formulas tab, click Insert Function.

      Insert Function option on the ribbon
  3. In the Formula Builder search box, type NETWORKDAYS.

  4. In the list that appears, double-click NETWORKDAYS.

  5. Under Arguments, click the box next to start_date, and then on the sheet, select cell B2 (6/8/2011)

  6. Under Arguments, click the box next to end_date, and then on the sheet, select cell C2 (9/20/2011)

  7. Leave the box next to holidays blank.

  8. Press RETURN .

    The result is 75 working days, not accounting for holidays.

  9. Select cell D2.

  10. To copy the formula into the remaining rows, drag the fill handle   Fill handle .

    Excel adjusts the cell references automatically to include the correct values for each row.

    Tip: See NETWORKDAYS function and NETWORKDAYS.INTL function to learn more about these formulas, including how to account for holidays.

Calculate the number of months between two dates in the same year

Excel treats months as values (1 through 12), which makes it easy to subtract an earlier date from another date as long as both dates fall in the same year. Use the MONTH function to obtain the month value for each date, and then calculate the difference between the values.

  1. Select cell D2, which is the first blank cell in the Duration column.

    If the cell is not blank, on the Edit menu, point to Clear, and then click All.

  2. In cell D2, type =MONTH(C2)-MONTH(B2), and then press RETURN .

    The result is 3.

Calculate the number of months between two dates in different years

When you have to find the number of months between dates that fall in different years, you can use a formula that determines the number of years between the two dates, converts the difference in years to months, and then accounts for any additional months to obtain an accurate result. Use the MONTH and YEAR functions to make this calculation.

  1. Select cell D3.

    If the cell is not blank, on the Edit menu, point to Clear, and then click All.

  2. In cell D3, type =(YEAR(C3)-YEAR(B3))*12+MONTH(C3)-MONTH(B3), and then press RETURN .

    The result is 9.

Calculate the number of years between two dates

Excel treats years as values. Use the YEAR function to obtain the year value for each date, and then calculate the difference between the values.

  1. Select cell D4.

    If the cell is not blank, on the Edit menu, point to Clear, and then click All.

  2. In cell D4, type =YEAR(C4)-YEAR(B4) and then press RETURN .

    The result is 2.

See Also

Subtract times

Show dates as days of the week

Insert and format the current date and time

Use Outlook Anywhere to connect to your Exchange server without VPN

Use Outlook Anywhere to connect to your Exchange server without VPN

In a local area network (LAN) environment, Outlook 2010 and Outlook 2007 communicate with Microsoft Exchange Server by using remote procedure call (RPC) with Transmission Control Protocol/Internet Protocol (TCP/IP). This method provides quick and efficient access in a corporate network.

Note:  A Microsoft Exchange Server account is required.

However, access to Exchange when you are outside of your organization's firewall, such as when you are at home or traveling, usually requires a virtual private network (VPN) connection to the organization's network. A VPN provides you with a connection within an organization's network and within its firewall. A VPN also enables access to more network services than those required for just e-mail access.

Connecting to Microsoft Exchange Server

For remote connections, Outlook offers Outlook Anywhere, an alternative to VPN connections that allows you to use Outlook just as you normally do at your organization, without the need for any special connections or hardware, such as smart cards and security tokens. Outlook can connect to Exchange through the Internet by using remote procedure call (RPC) over HTTP. The Outlook Anywhere feature allows you to access your Exchange account remotely from the Internet when you are working outside your organization's firewall.

Outlook Anywhere requires the following:

  • Your account is hosted on Microsoft Exchange Server 2016, Exchange Server 2013, Exchange Server 2010, Exchange Server 2007, or Exchange Server 2003.

  • For Exchange Server 2003, 2007, and 2010, your Exchange administrator must configure the server to permit connections via HTTP. Exchange administrators can find more information about configuring this feature in the Microsoft Office Resource Kit and in Microsoft Exchange documentation.

  • For Exchange Server 2013 or 2016, Outlook Anywhere is enabled by default, because all Outlook connectivity takes place via Outlook Anywhere. The only post-deployment task you must perform to successfully use Outlook Anywhere is to install a valid SSL certificate on your Client Access server. Mailbox servers in your organization only require the default self-signed SSL certificate.

    For more information, see Outlook Anywhere.

Enable Outlook Anywhere in Outlook

For Outlook 2007 and 2010, your Exchange administrator can automatically configure all copies of Outlook in your organization or provide a special executable script file that enables Outlook Anywhere. You can also manually configure Outlook Anywhere if the system requirements are met and you have the correct URL and security information from your Exchange administrator.

Outlook 2013 and 2016 no longer support manually configured Exchange profiles. To enable Outlook Anywhere, Outlook 2013 and Outlook 2016 administrators must configure Autodiscover. For more information, see Autodiscover service and Outlook 2016 Implementation of Autodiscover.

  1. Click the File tab

  2. Click Account Settings, and then click Account Settings.

    Account Settings in the Backstage view

  3. Select the Exchange account, and then click Change.

  4. Click More Settings, and then click the Connection tab.

  5. Under Outlook Anywhere, select the Connect to Microsoft Exchange using HTTP check box.

  6. To specify a proxy server, click Exchange Proxy Settings.

    Connection settings

    1. Type the URL provided by your Exchange administrator.

    2. If your Exchange administrator tells you to use a Secure Sockets Layer (SSL) connection, select the Connect using SSL only check box.

    3. If your Exchange administrator instructs you to do so, select the Only connect to proxy servers that have this principal name in their certificate check box, and then type msstd: followed by the URL provided by the administrator.

  7. Under Proxy authentication settings, click Basic Authentication or NTLM Authentication as instructed by your Exchange administrator.

    Proxy authentication settings

    Note: If you click Basic Authentication or NTLM Authentication and an LM Compatibility Level of less than 2, you will be prompted for a password each time a connection is made to Exchange. With Basic Authentication, the password is sent in clear text. For increased security, we recommend that you select the NTLM Authentication and Connect using SSL only options.

Top of page

  1. On the Tools menu, click Account Settings, select the Exchange account, and then click Change.

  2. Click More Settings, and then click the Connection tab.

  3. Under Outlook Anywhere, select the Connect to Microsoft Exchange using HTTP check box.

    Connection tab

    Note: If the Outlook Anywhere section is not available, your computer is probably not running Windows XP Service Pack 2 or a later service pack.

  4. To specify a proxy server, click Exchange Proxy Settings.

    Connection settings

    1. Type the URL provided by your Exchange administrator.

    2. If your Exchange administrator tells you to use a Secure Sockets Layer (SSL) connection, select the Connect using SSL only check box.

    3. If your Exchange administrator instructs you to do so, select the Only connect to proxy servers that have this principal name in their certificate check box, and then type msstd: followed by the URL provided by the administrator.

  5. Under Proxy authentication settings, click Basic Authentication or NTLM Authentication as instructed by your Exchange administrator.

    Proxy authentication settings

  6. Click OK.

    Note: If you click Basic Authentication or NTLM Authentication and an LM Compatibility Level of less than 2, you will be prompted for a password each time a connection is made to Exchange. With Basic Authentication, the password is sent in clear text. For increased security, we recommend that you select the NTLM Authentication and Connect using SSL only options.

Top of page

See also

Outlook Anywhere

Determine the version of Microsoft Exchange Server my account connects to