Home > Excel Help Portal > Dates and Times in Excel

# Dates and Times in Excel

## The ExcelUser portal offers links to help pages created by top Excel web sites. This section discusses calculating dates and times in Excel.

1. Conditional Formatting was introduced with Excel 97 and is a terrific feature, but there is a limit of 3 conditional sets per cell (like 3 wishes). Conditional Formatting, while in effect for a cell, will override the text colors that can be produced for numeric values by normal cell formatting. Filters, Validation, and... Keywords: AutoFilter, Criteria, Rank, Sorting, Dates, Days, Holidays, Download, Borders, Colors, Conditional Formating, Fonts, Gridlines, Patterns, Arrays, Errors, Formulas, Links, PivotTables, VBA, Macro, UDF, User Defined Function, VBE, Error Message, Toolbar, HTML, Web, Comments, TODAY, COUNTIF, MOD, SUBTOTAL, COUNTA, MATCH, FIND, WEEKDAY, EXACT, LARGE, TRIM, CELL, MAX, VALUE, OFFSET

2.
3. How to Use Microsoft Excel Data Tables to Analyze Information in a Database This article describes how to use Microsoft Excel tables to analyze information contained in a... KB#282851. Keywords: Criteria, Days, Format, Formulas, DSUM, MONTH, DCOUNT, DMAX

4.
5. How to use Dates and Times in Excel This step-by-step article describes how to use the built-in date and time functions in Microsoft Excel to perform complex date and time calculations. Excel stores all dates as integers and all times as decimal fractions. With this system, Excel... KB#214094. Keywords: Analysis ToolPak, SERIES, Sorting, Dates, Days, Months, Networkdays, Years, Custom Number Formats, Formulas, Files, TODAY, NOW, DATEVALUE, TIMEVALUE

6.
7. How to Create a Monthly Calendar This article contains a sample Microsoft Visual Basic for Applications macro (Sub procedure) that prompts you for the month and year and creates a monthly calendar using a... KB#150774. Keywords: Dates, Days, Borders, Fonts, Format, Gridlines, Errors, VBA, Macro, Web, Window

8.
9. How to Create a Monthly Calendar This article contains a sample Microsoft Visual Basic for Applications macro ( Sub procedure) that prompts you for the month and year and creates a monthly calendar using a Microsoft Excel 2000... KB#213795. Keywords: Dates, Days, Borders, Fonts, Format, Gridlines, Errors, VBA, Macro, Web, Window

10.
11. Office XP Proofing Tools Kit Hotfix Package: February 6, 2004 This article describes the Microsoft Office XP Proofing Tools issues that are fixed in the Office XP Proofing Tools Kit hotfix package that is dated February 6, 2004. back to the top Issues That Are Fixed in the Hotfix Package The following issue... KB#835717. Keywords: Dates, Errors, File Name, Files, Registry, Web

12.
13. Excel 2002 PostService Pack 2 Hotfix Package: August 28, 2003 This article describes the Microsoft Excel 2002 issues that are fixed in the Microsoft Excel 2002 Post-Service Pack 2 Hotfix package that is dated August 28, 2003. Issues that are fixed in the hotfix package The following issue is fixed in this... KB#827143. Keywords: Dates, Circular References, File Name, Files, Registry, Web

14.
15. Date Calculations A =IF(Month(\$B\$18)Month(DATE(YEAR(\$B\$18),MONTH(\$B\$18),1)+ MOD(4+7-WEEKDAY(DATE(YEAR(\$B\$18),MONTH(\$B\$18),1)),7)+28),"",DATE(YEAR(\$B\$18),MONTH(\$B\$18),1)+ MOD(4+7-WEEKDAY(DATE(YEAR(\$B\$18),MONTH(\$B\$18),1)),7)+28) January, 2002 has a 5th Wednesday and it is January 30, 2002. Except in a Leap Year,... Keywords: Analysis ToolPak, Charts, Dates, Days, Holidays, Workday, Years, Borders, Format, Formulas, UDF, VBA, HTML, Web, Comments, WEEKDAY, DATEDIF, NOW, MOD, OFFSET, WEEKNUM, ROW

16.
17. Excel 2000 PostService Pack 3 Hotfix Package: July 12, 2003 This article describes the Excel 2000 issues that are fixed in the Excel 2000 Post-Service Pack 3 Hotfix package that is dated July 12, 2003. Issues That Are Fixed in the Hotfix Package The following issue is fixed in this hotfix package.... KB#823809. Keywords: Dates, Errors, Error Message, File Name, Files, Registry, Web

18.
19. How to Use Grouping for Fields in a PivotTable In Microsoft Excel, you can group a field that is a date, a time, or a number. With date and time pivot fields, data in the table can be grouped into specific periods. With numeric pivot fields, data in the table can be grouped into evenly... KB#142153. Keywords: Days, Sales, PivotTables

20.
21. SUMIF Example Using SUMIF to get Subtotals by Date Sum all the amounts per date using a solution provided by Bill Manville (Oxford, England) May 7, 1998 in the microsoft.public.excel.misc newsgroup. caution: In order for this example to work the dates must be sorted or arranged so that same dates appear next to... Keywords: Criteria, Dates, Months, Download, Sales, Arrays, Formulas, CD-ROM, Web, Comments, COUNTIF, INDIRECT

22.
23. Clip Organizer 2002 postService Pack 2 hotfix package: October 24, 2003 This article describes the Microsoft Clip Organizer 2002 issues that are fixed in the Clip Organizer 2002 post-Service Pack 2 hotfix package that is dated October 24, 2003. back to the top Issues that are fixed in the hotfix package The... KB#829341. Keywords: Dates, File Name, Files, Registry, Web

24.
25. Incorrect file origin is listed when you open a text file in Excel 2002 When you open a text file (*.txt) in Microsoft Excel 2002, the Text Import Wizard may list an incorrect file origin (for example: Chinese Simplified, Japanese, or... KB#821456. Keywords: Dates, Download, File Name, Files, Registry, Text Files, Web

26.
27. Excel Appears to Stop Responding While Converting Text to Columns in Earlier Version Worksheet After you open an Excel worksheet that was created in an earlier version of Excel (for example, Microsoft Excel 97 for Windows), you may experience one of the following scenarios. Note In both of these scenarios, Excel uses 100 percent of your... KB#812668. Keywords: Dates, Arrays, Macro, File Name, Files, Web, Scenarios

28.
29. Office 2003 Input Method Editor (Chinese Traditional) hotfix package: March 5, 2004 Describes an issue that occurs when you use the Self-learning feature in the Input Method Editor (Chinese Traditional) and repeatedly type the same Chinese... KB#835740. Keywords: Dates, Download, Errors, Error Message, File Name, Files, Registry, Web

30.
31. Custom Toolbars Are Lost After You Quit Excel After you add custom toolbars to Excel 2002, quit Excel 2002, and then restart Excel 2002 either manually or from embedded Excel OLE objects, the custom toolbars may not... KB#330345. Keywords: Dates, OLE, File Name, Files, Registry, Toolbar, Web

32.
33. Year 2000 Wizards for Microsoft Excel 97 and 2000 Available for Download Microsoft has developed add-in tools that you can use with Microsoft Excel 97 and Microsoft Excel 2000. These can help you prepare dates in Excel workbooks for transitioning from earlier versions of Excel or for auditing workbooks for the year... KB#176943. Keywords: Add-In, Dates, Years, Download, Format, Links, Macro, Files, Web, Auditing, TODAY

34.
35. Date Returned in a Macro Is Four Years Too Early When you run a macro that uses a date from a worksheet cell, the date returned by the macro may be four years and one day earlier than the actual... KB#157035. Keywords: Dates, Years, VBA, Macro, Web

36.
37. Excel cannot complete this task with available resources" error message when you try to view the tracked changes in Excel 2002 Fixes a problem in Excel 2002 where if you password protect a shared workbook, you receive an error message when you try to view tracked changes. Requires that you install a hotfix to resolve the... KB#331863 ". Keywords: Dates, Errors, Password, Error Message, File Name, Files, Web

38.
39. Mailbox and Other PrinterSpecific Settings Are Saved with an Excel File When you send a Microsoft Excel workbook to another user account in an e-mail message or store the file on a network share, with the print settings set to print to your mailbox or to a local printer with specialized settings, these settings may... KB#829766. Keywords: Dates, Printing, File Name, Files, Registry, Web

40.
41. How to Use Grouping for Fields in a PivotTable In Microsoft Excel, you can group a field that is a date, a time, or a number. With date and time pivot fields, data in the table can be grouped into specific periods. With numeric pivot fields, data in the table can be grouped into evenly... KB#214259. Keywords: Dates, Days, Sales, PivotChart, PivotTables, COLUMN, ROW

42.
43. Date Returned in a Macro Is Four Years Too Early When you run a macro that uses a date from a worksheet cell, the date returned by the macro may be four years and one day earlier than the actual... KB#213593. Keywords: Dates, Years, VBA, Macro, Web

44.
45. The CPU Usage Is 100 Percent When You Copy a Large Sheet in Excel If you use the following method to copy a whole sheet in Excel 2002, the CPU utilization may reach 100 percent, and it may take a long time to complete the requested action: Right-click a sheet in Excel 2002. Click Move or Copy . Click to select... KB#324051. Keywords: Dates, Format, File Name, Files, Registry, Web

46.
47. How to calculate Compound Interest for an IntraYear Period in Excel 2000 The future value of a dollar amount, commonly called the compounded value, involves the application of compound interest to a present value amount. The result is a future dollar amount. Three types of compounding are annual , intra-year , and... KB#213907. Keywords: Add-In, Analysis ToolPak, Formulas, EFFECT

48.
49. How to Calculate Compound Interest for an IntraYear Period The future value of a dollar amount, commonly called the compounded value, involves the application of compound interest to a present value amount. The result is a future dollar amount. Three types of compounding include: annual, intrayear,... KB#162634. Keywords: Add-In, Analysis ToolPak, Years, Formulas, EFFECT

50.
51. You Receive Error Messages When You Delete Multiple Columns from a Graph If you try to delete multiple columns from a Microsoft Graph file that is embedded in a Microsoft PowerPoint presentation slide, you may receive the following error message: CiceroUIWndFrame: GRAPH.EXE - Application Error The instruction at... KB#323613. Keywords: Dates, Errors, File Name, Files, Web

52.
53. Workbook Does Not Print When You Use Excel on a Tablet PC When you use Microsoft Excel on your Tablet PC and you print an Excel workbook, some text in the workbook may not print. Note When you use the Print Preview feature to view the Excel workbook, all the text in the Excel workbook appears... KB#821273. Keywords: Dates, Printing, File Name, Files, Web

54.
55. Use group in Pivot Tables to add Years Steve, a client from the Carolinas, asked the question which lead to this tip. It is common for data imported from an external system to have a date field. There is a simple method with in a pivot table to have the date field converted to weeks, months, quarters or years. MrExcel.com provides... Keywords: Dates, Days, Months, Years, PivotTables, VBA, Web

56.
57. Excel functions for calculating loan repayments Banks generally set repayments on loans and mortgages in equal payments over a fixed period of time. Within these regular payments the mix of interest and principal changes as time passes. A single formula can easily calculate the repayments on a loan of £x,000 at y% over z years. The real world is... Keywords: SERIES, Days, Months, Years, Formulas, Links, Sounds, PMT, CUMPRINC

58.
59. "Unable to Read File" Error Message When You Open a Workbook When you try to open a workbook, you receive the following error message: Unable to read file. After you click OK , the Document Recovery task pane does not appear, and the workbook is not... KB#812384. Keywords: Dates, Errors, Corrupted, Error Message, File Name, Files, Web

60.
61. Incorrect Results When You Perform Mail Merge with Excel Data Source When you perform a mail merge with a Microsoft Excel data source, the results of the mail merge may not be correct. For example, a record that contains a blank field in the data source displays the data from the previous record in the merge... KB#204542. Keywords: Dates, DDE, File Name, Files

62.
63. Method to Determine Whether a Year Is a Leap Year This article contains information that explains how to determine whether the year in a date used in a Microsoft Excel document is a leap... KB#214019. Keywords: Days, Years, Errors, Formulas

64.
65. Error Message: RunTime Error:'13' Type Mismatch When you click a macro or you click a function on a menu that was created by another macro, you may receive the following error message: Run-time error:'13' Type... KB#821292. Keywords: Dates, Errors, Macro, File Name, Files, Web

66.
67. Write a Calendar File We have a sacred duty in our department; probably in yours. Somebody has to bring donuts to staff meeting on Friday. I was determined to find a way to generate Calendar Files that I could send to the interested party each week. In the end, I created a monster (that I will share with you. The... Keywords: Days, Holidays, Printing, VBA, Macro, Files, Web

68.
69. Excel FAQ - Dates and Times 25, 50 47, etc... the result I would like is 1:10, 1:50: 2:03 Since XL stores times as fractions of days, in order to convert integers into times you need to divide the sum by (24*60*60) or 86,400 1 is a day; 24 hours. So one hour is 1/24. =A1*B1*24 will bring the desired result. Format result... Keywords: SERIES, Dates, Months, Years, Custom Number Formats, Web

70.
71. Changing an Integer to an Excel Serial Number Many programs export dates as integers. This article contains a sample Microsoft Visual Basic for Applications macro ( Sub procedure) that transforms an integer date into a Microsoft Excel serial... KB#213446. Keywords: Dates, Export, Format, VBA, Macro, Web

72.
73. Excel functions for dates and times There are many functions in Excel which allow you to manipulate dates and times and perform calculations on them. The principle behind these is simple and much of the confusion relates to how the results are displayed - i.e. how your dates are formatted. If you type a date or time (e.g. 23/1/03 or... Keywords: Dates, Days, Months, Format, Styles, Formulas, NOW, SECOND, DATEDIF, CHOOSE, WEEKDAY

74.
75. How to use NETWORKDAYS with Multiple Holidays in Excel 2000 This step-by-step article describes how to use the NETWORKDAYS function with multiple holidays in Microsoft Excel 2000. When you use the NETWORKDAYS function with multiple holidays, you can enter the holidays as cell references or in the serial... KB#259200. Keywords: Analysis ToolPak, Dates, Days, Holidays, Networkdays, Format, Arrays, Errors, Formulas, DATEVALUE

76.
77. Sample Macro to Determine If a Year Is a Leap Year In Microsoft Excel, you can determine whether a date occurs during a leap year by using a Microsoft Visual Basic for Applications... KB#266672. Keywords: VBA, Macro, Web, Window

78.
79. AutoCalculate feature is working even if that feature is turned off Fixes the problem where the AutoCalculate feature still functions when you change the color scheme for the display or update Group... KB#325703. Keywords: Dates, Colors, File Name, Files, Web

80.
81. How to Calculate the Number of Days in a Month In Microsoft Excel, you can use date functions to calculate the number of days in a month. For example, you can use the following formula to return the number of days in a month, based on a given starting date DAY(DATE(YEAR( The_Date... KB#214313. Keywords: Days, Formulas, VBA, Web

82.
83. How to Use Application.Match to Locate Date Values on a Worksheet In Microsoft Excel, you may run into problems if you use the Match method in a Microsoft Visual Basic for Applications macro or procedure to locate date values on a worksheet. When you use a macro to search for dates, you need to search for the... KB#213643. Keywords: Dates, Errors, VBA, Macro, Web

84.
85. Workbook Is Slow to Open When you open an Excel 2002 workbook, it may take a long time before the workbook... KB#327365. Keywords: Dates, Formulas, File Name, Files, Web, VLOOKUP

86.
87. Calculating Depreciation with the Production Method Microsoft Excel includes functions to calculate straight line (SLN), sum of the years digits (SYD), and double-declining balance (DDB) depreciation. A fourth depreciation method, the production method, is widely used in business to... KB#213497. Keywords: Years, Formulas, VBA, Macro, Web

88.
89. Write an ICS We have a sacred duty in our department; probably in yours. Somebody has to bring donuts to staff meeting on Friday. I was determined to find a way to generate Calendar Files that I could send to the interested party each week. In the end, I created a monster (that I will share with you. The... Keywords: Days, Holidays, Formulas, Links, Printing, VBA, Macro, Files, Web

90.
91. More Select Case Sometimes it is easier to look for a common condition rather that write multiple IF statements. These examples illustrate different kinds of conditions for “Select Case”. This first example is in the middle of some code that is called from one of two different macros; whichever one sends its own... Keywords: Years, Colors, Format, Errors, VBA, Macro

92.
93. Excel Quits When You Click an ActiveX Control When you click a Microsoft ActiveX control immediately after you click another ActiveX control on a worksheet, Excel may... KB#330157. Keywords: Dates, ActiveX, File Name, Files, Web

94.
95. How to Call Microsoft Excel Macros That Take Parameters Using Automation, you can manipulate Microsoft Excel. It is possible to call macro procedures that are stored in Microsoft Excel Workbooks by using the Run method of the Microsoft Excel Application object. Microsoft Excel macro procedures that... KB#153307. Keywords: Years, VBA, Macro, Object

96.
97. Index Server 3.0 Does Not Correctly Index Some Excel Files Index Server may not correctly index some Excel files because of problems parsing certain character... KB#327012. Keywords: Dates, File Name, Files, Index Server, Web

98.
99. How to Programmatically Format Names of Months in AllCapital Letters In the built-in date format in Microsoft Excel, only the first letter of the month is uppercase. You can create a Microsoft Visual Basic for Applications macro that can be used to capitalize the entire month... KB#213503. Keywords: Months, Format, VBA, Macro, Object, Web

100.
 Excel User's Home ExcelUser Blog Site Map Contact Excel for Business Excel Dashboards Excel Solutions Exploring Excel BI for Excel Business Tools Excel Catalog Affiliate Program Excel Help Portal

ExcelUser, Inc.
http://www.ExcelUser.com

Copyright © 2004 - 2012 by Charles W. Kyd, all rights reserved. Content, graphics, and HTML code are protected by US and International Copyright Laws, and may not be copied, reprinted, published, translated, hosted, or otherwise distributed by any means without explicit permission. Terms of Use | Privacy Policy | Earnings Policy.