Home > Excel Help Portal > Dates and Times in Excel

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

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.

51. MSQuery Uses 1900 Date When Two Digits Are Used for Year When you perform a query in Microsoft Query 97 for Windows, and you use two digits for a year in the criteria, Microsoft Query may return records that meet criteria other than the criteria you... KB#187698. Keywords: Criteria, Query, Dates, Years, Toolbar, Web

 
52. How to modify a Particular Time with the TIME and MOD Functions in Excel 2000 This step-by-step article shows you how to add and subtract any number of hours, minutes, or seconds from a specific time of day. In Microsoft Excel 2000, time values are represented with serial numbers that range from 0 (12:00:00 A.M.) to... KB#214095. Keywords: Dates, Formulas, TIME, MOD

 
53. How to use the Auto Fill Options button in Excel When you fill in data based on adjacent cells by dragging the fill handle, the Auto Fill Options button is displayed just below and to the right of your filled selection. When you click the Auto Fill Options button, a list of options appears... KB#291359. Keywords: SERIES, AutoCorrect, Dates, Days, Months, Years, Format, Errors, Smart Tag

 
54. Days of the Week Before March 1, 1900 Are Incorrect When you use the WEEKDAY function to compute the day of the week for dates prior to March 1, 1900, the function returns an incorrect result. For example, if you use the WEEKDAY function to determine the day of the week for February 16, 1900,... KB#106339. Keywords: Dates, Errors, Formulas, WEEKDAY

 
55. Dates Inserted by Recorded Macro May Be in Wrong Century In the versions of Microsoft Excel listed at the beginning of this article, if you enter a date in a cell while recording a Microsoft Visual Basic for Applications macro, the macro may enter the wrong date when you run it. Specifically, the date... KB#180159. Keywords: Years, Macro, VBA

 
56. A Visual Basic Nested Case Statement Example In Microsoft Excel 2000, you can use nested case statements in Visual Basic to execute one of several expressions based on a particular value or expression evaluation similar to an IF... KB#213473. Keywords: Dates, VBA, Macro, Web

 
57. How to Display the Current Date and Time with Text You can enter the current date and time as text by using the TEXT function with the TODAY or NOW functions. For example, to display Today is 2/18/98 where 2/18/98 is the current date, use the following formula: Today is TEXT(TODAY(),m/d/yy")... KB#214093. Keywords: Charts, Format, Formulas, TEXT, TODAY, NOW

 
58. Excel Pivot Table Date Order MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as... Keywords: Dates, Months, Sales, PivotTables, VBA, Web

 
59. Advanced Pivot Table Operations You probably know how to create a basic pivot table. Here are some obscure pivot table tricks which will help you get the most out of pivot tables. Pivot Table Wizard The Basics: At right, you can see the familiar old basic default pivot table. Regions and products are sorted alphabetically, totals... Keywords: Dates, Sales, PivotTables, VBA, Web

 
60. Help using Excel spreadsheet functions - text, abs The Text and '&' functions are very useful for including your spreadsheet data within meaningful sentences which can be understood by a layman. Statements can be constructed which include numeric values taken from cells. The '&' symbol is not strictly a function. It is an operator which can be used... Keywords: SERIES, Dates, Custom Number Formats, Symbol, Formulas, Menu Command, TEXT, ABS, NOW

 
61. IF THEN in worksheet functions and in VBA Help does not put the = in front of the worksheet formulas, because they can be stacked inside one another, but this can lead to confusion. Look at the syntax in Excel HELP for IF... Keywords: Dates, Download, Formulas, VBA, Web, Comments, VLOOKUP

 
62. Examples of Interest Calculations Outside the United States Microsoft financial functions presume customary financial practices in the United States. Other countries have different laws about quoting and computing interest. This article provides examples that demonstrate how to compensate for these... KB#294396. Keywords: Months, NOMINAL, FREQUENCY

 
63. How to work with Serial Number Dates and Julian Dates in Microsoft Excel 2000 This article explains how to convert serial number dates to Julian dates and how to convert Julian dates to serial number dates in Microsoft Excel 2000. Julian dates refer to the number of days from the first of the year and the number of days... KB#214099. Keywords: Dates, Days, Formulas, Defined Name, TEXT, DATEVALUE

 
64. SORTING, some notes on sorting in Excel Keywords: Dates, Days, Years, Custom Number Formats, Web, Comments, Window, NOW

 
65. The Excel DATEDIF function While it is easy to determine the number of days between any two dates simply by subtracting one from the other, it is extremely difficult to return a string that says something like '24 years, 6 months and 8 days'. The maths and logic involved in this apparently straightforward question are very... Keywords: Dates, Days, Months, Years, Formulas, Files, DATEDIF, NOW

 
66. Formatting Time Values Since then, traffic has picked up tremendously. I am about to hit my one-millionth page view for the year 2001. There is enough consulting work to keep myself busy, plus more projects for Juan Pablo and Mala and occassionally Access projects for Mike and data analysis for John. People are simply... Keywords: Days, Years, Format, Sounds, Macro, VBA, Web

 
67. Automatically get the date and time Since most of the MrExcel readers read the page at work, I'll start with an introduction here. I was reading the local newspaper and they had an article about the trendy, new, easy-to-use Weblog technology. Basically, it is easy to create a daily posting to your web site, without having to go... Keywords: Days, VBA, Web, NOW, TODAY

 
68. How to convert Dates in Imported Files from yy/mm/dd to mm/dd/yy Format in Excel 2000 This step-by-step article describes how to convert dates in imported files from yy/mm/dd to mm/dd/yy format in Excel 2000. Files imported from mainframe computers and other computer systems may contain dates in the format yy/mm/dd. These date... KB#214031. Keywords: Dates, Format, Formulas, Files, DATEVALUE

 
69. How to Return a Day of the Week for a Date That Is Contained in a Cell in Excel This article describes how to return a day of the week for a date that is contained in a cell in Microsoft... KB#827327. Keywords: Dates, Format

 
70. Excel 2000: Excel Quits When GetPivotData Function Is in Second Workbook If you create a GetPivotData function in Microsoft Excel 2000, Excel may quit unexpectedly. This problem occurs if: The PivotTable is in one workbook, and the GetPivotData function is in another workbook. The PivotTable contains a date... KB#324391. Keywords: Dates, Format, Formulas, PivotTables, File Name, Files

 
71. Maximum Times in Microsoft Excel In Microsoft Excel, there are limits to the maximum time values that you can enter into a cell or return by using a formula. This article explains those... KB#214386. Keywords: SERIES, Dates, Format, Formulas

 
72. Calculation Problems When Custom Function Creates a Defined Name When you press ENTER after typing a function into a worksheet, the worksheet may not be calculated correctly, and the message Calculate may remain in the status bar, instead of disappearing when you press... KB#248179. Keywords: Defined Name, TODAY, RAND, AREAS, INDEX, OFFSET, CELL, INDIRECT, ROWS, COLUMNS, NOW

 
73. How to turn AutoComplete On or Off in Excel 2000 This step-by-step article describes how to turn the AutoComplete feature on or off. (The AutoComplete feature automatically completes entries in columns.) This information is also covered in more detail in Microsoft Excel 2000 Help. For... KB#263552. Keywords: Patterns

 
74. Return End Of Month Date There are a few ways to solve many problems in Excel. I am sure someone will come up with something simpler, but at the moment, this formula comes to mind:=DATE(YEAR(E1),1+MONTH(E1),1)-1The Date() function generally accepts three arguments: A year, a month, and a day. So, December 13 2001 could be... Keywords: Formulas, VBA, Web, DATE

 
75. How to calculate the Number of Months Between Two Dates in Excel 2000 This step-by-step article shows you how to calculate the number of months between any two dates. To calculate the number of months between any two dates, use one of the following methods. Note that both methods use the following information: EDate... KB#214134. Keywords: Dates, Days, Months, Formulas

 
76. You Cannot Reopen a File After You Save It If you save certain files in Excel 2000, you may not be able to reopen... KB#324312. Keywords: Dates, File Name, Files

 
77. Excel site index Microsoft Excel 2002 Formulas (With CD) Index lists created using 'MeadInKent... Keywords: Charts, SERIES, Criteria, Rank, Dates, XML, Format, Styles, Arrays, Errors, Formulas, Macro, VBA, HTML, Hyperlinks, Web, Validation, FV, PMT

 
78. Formula to add years to a given date In Bob's case, you would not need a formula. If he has 1-Jan-2001 in A2 and 1-Feb-2001 in A3, he can highlight A2:A3, then click the fill handle in the lower right corner of the selection, drag down, and the series should correctly extend. MrExcel.com provides examples of Visual Basic procedures... Keywords: SERIES, Dates, Years, Formulas, VBA, Web, MONTH, DAY

 
79. Mapinfo Professional to create Excel Maps Use MapInfo Professional tables with Data Map to create custom territories. Alas, this tip only applies up through Excel 2000. Microsoft sacked the DataMap functionality in Excel XP. If you are using Excel XP, you will want to investigate the MapPoint program. Have you ever tried the data map... Keywords: Years, Sales, VBA, Web

 
80. How to Calculate the Number of Months Between Two Given Dates This article contains information about calculating the number of months between any two given... KB#67093. Keywords: Dates, Days, Months, Years, Formulas, NOW

 
81. Convert daily dates to monthly dates 11/21/98: Kevin at Amerimail.com sent in this week's problem. I have a table of events in Excel. Each row has a date indicating the day that the event occurred. I want to use a pivot table to summarize the data, but I want to see it at the month level instead of at the daily level. I tried simply... Keywords: Dates, Days, Format, Formulas, PivotTables, VBA, Web

 
82. Rearrange a Text File My friend came to me with a text file that he needed to extract 3 elements on suceeding lines and re-arrange them into a new order. Bringing the 4,800 row text file directly into Excel caused things to be split in an uncomfortable order and was going to take a lot of programming. Hope this... Keywords: Format, Arrays, Printing, VBA, Text Files, Parse

 
83. Write a Formula that always points to the same column, but is free to move to different rows as it is copied I solved this by placing dollar signs ($B$34) and then copying down, but when I try to copy everything and set up the next month, it copies $B$34 to the next month also. This is wrong because my new sales total is C34 not not B34.Do you know how I can get around this?You'll notice there are two... Keywords: Months, Sales, Formulas, VBA, Web

 
84. Formulas That Return the Last or First Day of a Month This article describes how to create a formula that returns the last or first day of the current... KB#214101. Keywords: Add-In, Analysis ToolPak, Months, Errors, Formulas, EOMONTH

 
85. Excel -- Pivot Tables -- Grouping Data Grouping Data In a Pivot Table, you can group the items in a Row or Column field. For example, items in a date field can be grouped by month, and items in a number field can be grouped by tens. Grouping Dates To group the items in a Date field Grouping Dates by Week To group the items in a Date... Keywords: Dates, Days, Download, Errors, Error Message

 
86. Excel -- Data Validation -- Custom Validation Criteria Examples Custom Validation Criteria Examples Prevent Duplicates Prevent duplicate entries in a range on the worksheet. In this example, Employee Numbers will be entered in cells B3:B10. Limit the Total Prevent entry of a value that will cause a range to exceed a set total. In this example, the total budget... Keywords: Criteria, Dates, Formulas, Validation, COUNTIF, TRIM, WEEKDAY

 
87. Setup Print in Macro I deliver a massive list to lots of people every month. Many of them are casual Excel users and simply want a spreadsheet that prints correctly the first time. I establish the headers and footers and apply them as the setup continues. Note that the date of the report is hard-coded this way so... Keywords: Months, Printing, VBA, Macro, Setup

 
88. Date and Time Tutorial Number of Days, Months and Years Between Two Dates You can create a Visual Basic for Applications User Defined Function to return the number of years, months and days from a start date to an end date. Use the Function in formulas or to create a text string: "As of yy/yy/yy, nn Years, mm Months and... Keywords: Dates, Days, Holidays, Months, Networkdays, Years, Format, Formulas, OnEntry, User Defined Function, VBA

 
89. Determine Years, Months, & Days between 2 Dates The next challenge is that one of these friends says that a tombstone says, “Died 10 October 1922 aged 50 years, 100 days”. What was the date of birth? Did the counter account for leap years? Excel cannot handle a date before 1900. I made one minor change because I always use ‘Option Explicit’ and... Keywords: Dates, Days, Months, Years, Formulas, Links, VBA

 
90. Print a Multiple Sheet Workbook with Page Numbers I was in someone's office and found they had a workbook with about 40 linked sheets. They were jumping through hoops trying to print the sheets with page numbers as though they were all on the same sheet. Here is a quicky to make the nice page numbering AND print the whole document in one swoop.... Keywords: Printing, Setup, Window

 
91. Stamp Auction application -- Dialogs, input boxes, UserForms Sometimes when I am feeling real mellow, I will write a whole application contained in a spreadsheet. This one exceeded all of my expectations. It is an elegant solution to a thorny problem. My stamp club conducts an auction at each meeting. Before the newsletter goes out, everyone lets our... Keywords: VBA, Window

 
92. Parsing Dates One option is to use an event handler, somewhat like the military time tip at http://www.mrexcel.com/tip029.shtml. Another method would be a formula. The Date() function allows you to specify a date by giving the Year, Month, Day. The following formula with parse your value and convert it to a... Keywords: Dates, Formulas, VBA, HTML, Web, Parse

 
93. Excel Code Examples Several of my genealogy friends had asked for a simple formula to determine the number of days between 2 dates. I was able to do years and days but months escaped me. Finally someone came through. The next challenge is that one of these friends says that a tombstone says, “Died 10 October 1922 aged... Keywords: Dates, Days, Months, Years, Formulas, VBA

 
94. Working with Dates To do this you’ll have to use a formula. If the starting date (30-june-2002) is in A2, put this formula in A3 and drag down.=DATE(YEAR(A2),MONTH(A2)+2,0) MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not... Keywords: Formulas, VBA, Web, DATE

 
95. Formatting Time Values For example, if I have 16548 seconds it should show 4:35:48"First, it's important to remember that Excel keeps dates informatios as day fractions. Therefor, to show the value it first needs to be converted like this:Divide el value in seconds (16548) in (60 s / min * 60 min/hr * 24 hr/day) to... Keywords: Dates, Format, VBA, Web

 
96. Use Date in Naming Convention Keywords: Save A File

 
97. Macros - Custom Excel Macros in VBA from Mr Excel Excel is a registered trademark of the Microsoft Corporation.MrExcel is a registered trademark of the Tickling Keys, Inc. All contents Copyright 1998-2003 by MrExcel... Keywords: Macro, VBA, CD-ROM

 
98. Number of Days, Months and Years Between Two Dates Create a User Defined Function that uses the DateSerial function to calculate the time elapsed between two dates. You can create a Visual Basic for Applications User Defined Function to return the number of years, months and days from a start date to an end date. Use the Function in formulas or to... Keywords: Dates, Days, Months, Years, Download, Format, Formulas, User Defined Function, VBA

 
99. Using Built-In Workday Calculation Functions Example of using workday and networkdays functions You can use Excel's built in Workday and Networkdays functions to perform calculations with dates taking into account weekends and holidays. Workday returns a date some number of workdays in the future. Networkdays calculates the number of workdays... Keywords: Dates, Days, Holidays, Networkdays, Download, Format

 
100. Calculate the day of the year and days remaining If you've ever had to figure out which of the year's 365 days a particular date falls on, or how many days remain in the year, you've probably found that Excel lacks functions to perform the calculation. But you can create formulas to do the job. The formula below returns the day of the year for a... Keywords: Charts, Days, Format, Formulas, Printing, DATE

 

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