Home > Excel Help Portal > Dates and Times in Excel

<<  (Page 3)  Previous              Next  (Page 2)  >>

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. 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

 
3. 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

 
4. 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

 
5. 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

 
6. 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

 
7. 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

 
8. 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

 
9. 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

 
10. 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

 
11. 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

 
12. 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

 
13. 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

 
14. 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

 
15. 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

 
16. 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

 
17. 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

 
18. 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

 
19. 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

 
20. 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

 
21. 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

 
22. 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

 
23. 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

 
24. 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

 
25. 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

 
26. 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

 
27. 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

 
28. 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

 
29. 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

 
30. "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

 
31. 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

 
32. 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

 
33. 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

 
34. 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

 
35. 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

 
36. 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

 
37. 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

 
38. 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

 
39. 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

 
40. 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

 
41. 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

 
42. 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

 
43. 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

 
44. 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

 
45. 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

 
46. 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

 
47. 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

 
48. 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

 
49. 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

 
50. 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

 

<< (Page 3)  Previous              Next  (Page 2) >>