Today’s Date Microsoft Word
Today's Date Microsoft Word
Dates and Date Functions in Microsoft Office Excel
Being numbers, dates can be sorted or used in calculations and there are several Excel worksheet functions that can be used with dates. This article explains dates, date formatting and date functions in Excel, and includes a few tips about using the fill handle.
Excel supports two date systems. The 1900 date system is the default on computers running Windows operating systems and the earliest date (serial value 1) in that system is January 1, 1900. The 1904 date system is the default on Apple Macs where date serial number 1 is January 2, 1904. The date system can be changed in Excel Options, but that's not really necessary because Excel automatically adjusts the dates if a file created in Excel for Windows is opened on a Mac or vice versa.
There is an error in the 1900 date system. Excel considers 1900 to be a leap year, but it's not, so there is a February 29 in 1900 in Excel for Windows. That means weekdays displayed for dates from February 1 to 29, 1900 and calculations using dates before March 1, 1900 are out by a day. That probably won't bother you, but keep it in mind.
When a date is entered into a cell with a two digit year, e.g. 9/5/11, Excel interprets the century. If the year is 00 to 29 Excel assumes it is 2000 to 2029. If the year is 30 to 99 Excel assumes it is 1930 to 1999. That can be over-ridden by entering a date with a four digit year, e.g. 9/5/1911. The way Excel interprets centuries can be changed in Control Panel, Regional and Language Options, Regional Options, then click on the Customize button and select the Date tab.em
Cells can be formatted to display dates in various ways by right clicking the cell, selecting Format Cells from the pop-up menu, then selecting Date on the Number tab. The date formats that can be selected in the "Type" field are determined by the country selected in Control Panel, Regional and Language Options, but can be over-ridden by changing the "Locale (location:)" field on the Format Cells dialogue box, e.g. from English (Australia) to English (US).
For example, if 9/5/11 is entered into a cell in English (Australia) format (where dates are shown as day, month, year) it could be formatted as 9/5/11, 9/05/2011, 09-May-11 or Monday, 9 May 2011. In the US, where month, day, year format is used, the same date entered as 5/9/11 could be formatted as 05/09/11, 5/9/2011, May 9, 2011, or 9-May-2011. Alternatively, a date can be displayed as a number by formatting the cell as a number without decimal places (May 5, 2011 as a number is serial value 40672 in Excel for Windows).
Dates can also be custom formatted. For example, the above date custom formatted as "ddd" would display in the cell as Mon or if it was custom formatted as "dddd" it would display as Monday. It could also be custom formatted as "mmm" to display May or as "mmmm, yyyy" to display May, 2011.
The following date functions (each shown with its syntax and a brief explanation) can be used in Excel. It might be easier to follow the examples below on a worksheet, so to start with, on a new (blank) worksheet, enter 2011 into cell A1, 5 into cell A2 and 9 into cell A3 -- and when copying the formulas below, make sure you copy from the = sign to the last closed bracket. The date serial values mentioned below relate to Excel for Windows. In the 1904 date system on the Mac they will be different.
DATE (year, month, day) returns the serial number that represents a date. For example, copy the formula =DATE(A1,A2,A3) and paste it into cell A4 on your example worksheet. It will return 40672, which is the serial value for May 5, 2011 and that date will be displayed in default date format in the cell.
Numbers and nesting can be included in this function to add a number of years, months or days to a date. Copy =DATE(YEAR(A4),MONTH(A4)+18,DAY(A4)) into cell A5 on your worksheet. It will add 18 months to the date in cell A4 and return 41222, which is the serial value for November 5, 2012.
Now enter the formula =A5-A4 into cell A6. It will display 550, which is the number of days between May 5, 2011 and November 5, 2012.
DATEVALUE (date_text) converts a date in the form of text to a number. Format cell A7 as text then enter 09-May-11 into that cell. Then copy =DATEVALUE(A7) into cell A8. It will return the serial value 40672, which as you read above, is the date value for that date. The next three functions (with 40672 still showing in cell A8) will show that is so.
DAY (serial-number) returns the day (1 to 31) from a date, so if =DAY(A8) is entered into cell A9, it will return the day, 9.
MONTH (serial-number) returns the month (1 to 12) from a date, so =MONTH(A8) entered into cell A10 will return the month, 5.
YEAR (serial-number) returns the year (1900 to 9999) from a date, so =YEAR(A8) entered into cell A11 will return the year, 2011.
TODAY is a simple function (with no arguments) that enters today's date into a cell. It updates every day to change the date in the cell to the current day's date (which is actually the date set on your computer). Enter =TODAY() into cell A13 on your example worksheet (yes, we left cell A12 blank) and if the function doesn't display today's date correctly, adjust the date on your computer.
Now, let's set up your worksheet for the next function. Enter your date of birth into cell A12.
DATEDIF (first_date,second_date,return_type) returns the difference between two dates in a variety of different ways as specified in the return_type. Return-type is the letter "d", "m", "y" or a combination thereof (entered into the function with the quotation marks, or entered into a cell without quotation marks if the function will refer to the cell(s) in the return_type argument -- see example below).
Although DATEDIF has been available as a function in Excel for 15 or more years, it is not included in Excel Help documentation, is not included in the drop-down list of Date and Time functions on the Insert Function dialogue box and can't be accessed by clicking on the fx button. To use DATEDIF it is therefore necessary to type the function directly into a cell -- but knowing how to use it can be handy.
Copy the formula =DATEDIF(A12,A13,"d") and paste it into cell A14. The first_date is your date of birth, the second_date is today's date and the result shown in cell A14 is the number of days between those two dates. The formula =A13-A12 would have produced the same result.
Now type the letter d (without quotation marks) into cell B15 then copy =DATEDIF (A12,A13,B15) into cell A15. Same result... your age in days.
Next, copy the formula =DATEDIF (A12,A13,"m") into cell A16 and = DATEDIF (A12,A13,"y") into cell A17. The results are your age in completed months and completed years.
Return_type "yd" gives the number of days ignoring the years, "ym" gives the number of months, ignoring the years and "md" gives the number of days, ignoring the months and years. Try editing the formula in cell A16 or A17 to see how these work.
Now, let's include some text and concatenation (using the ampersand) to see what we can come up with. Copy the following formula (from the = sign to the " after the word old) into cell A18...
="Today I am "&DATEDIF (A12,A13,"y")&" years, "&DATEDIF(A12,A13,"ym") &" months and "& DATEDIF (A12,A13,"md") &" days old"
There are several other date functions, some of which are only available from Excel 2007 onwards.
DAYS360 (start_date,end_date,method) returns the number of days between two dates based on a 360-day year (twelve 30-day months).
EDATE start_date,months) returns the serial number of the date that is the specified number of months before or after start_date.
EOMONTH (start_date,months) returns the serial number of the last day of the month before or after a specified number of months.
NETWORKDAYS (start_date,end_date,holidays) returns the number of whole work days between two dates, excluding specified holidays. This function considers Monday to Friday as work days and holidays can be specified by dates or serial values.
WEEKDAY (serial_number,return_type) returns a number between 0 and 6 or between 1 and 7 (depending on the return_type) that identifies the day of the week. It's easier to use custom format "ddd" or "dddd".
WEEKNUM (serial-num,return_type) converts a serial number (date) to a number representing the week number (numerically) in a year. There are a few quirks to this function -- type WEEKNUM into the search field of Excel Help for details.
WORKDAY (start_date,days,holidays) returns the serial number of the date before or after a specified number of work days, excluding holidays.
YEARFRAC (start_date,end_date,basis) returns the fraction of a year that is the number of whole days between start_date and end_date.
Now, here are a few interesting things to try on your worksheet.
Add days to a date... Enter a date into cell A19. Any date will do -- and format it however you like. Then in cell A20 enter the formula =A19+1 to add one day to the date in cell A19. Next, copy the formula and paste it into cells A21 to A25.
Convert to values... Select cells A20 to A25 and convert the formulas to values (copy, right click over one of the cells, select Paste Special on the pop-up menu, then select Values and click on OK. Then press Esc to turn off the marching black ants).
Fill dates down... Select the bottom couple of cells and drag the fill handle in cell A25 down about 40 or 50 rows. (The fill handle is the small black square at the bottom right of the selected cells). When the mouse button is released, the Auto Fill Options button will appear next to the bottom cell. Click on that button and select Fill Weekdays on the pop-up menu. Now look carefully at the dates.
See what day each date is... Scroll back up to row 20 and enter the formula =A20 into cell B20. Then custom format cell B20 as "ddd". To do that, right click over B20, select Format Cells, select Custom on the Number tab of the Format Cells dialogue box, then overtype whatever is in the Type field with ddd (without quotation marks).
Fill the days down... With cell B20 selected, double click on the fill handle to put the day into each cell next to the dates. Excel has copied the formula from cell B20 down, adjusting the cell reference in each cell and has also copied the formatting.
Show Year and Month in one cell... In a database containing a column of dates it is often desirable to filter or summarise the data by months, and an easy way to do that is to add a column that shows the year and month (as a decimal number) alongside each date. Here's how.
Select cell C20 and format it as a number with two decimal places (do not show the comma separator). Then copy the formula =YEAR(A20)+(MONTH(A20)/100) into cell C20 and double click on the fill handle to copy the formula (and formatting) down. Then insert three blank rows above row 20 (select rows 20 to 22, right click, Insert) and enter some numbers into column D from cell D23 to the bottom of the database (enter 1, 2, 3 into cells D23, D24 and D25 then double click on the fill handle).
Put the column headings Date, Day, Y.m and No into cells A22 to D22, make them bold and centred in their cells. Next, turn auto filter on (select a cell in the database, then in Excel 1997-2003 click on Data, Filter, Auto Filter or in Excel 2007 or later click on the Data tab then the Filter icon). Now copy this formula =SUBTOTAL(9,D23:D100) into cell D21 and filter the database for one of the months in the Y.m column. Take note of the subtotal, then filter Y.m for a different month and look at the subtotal.
So there you have it... Dates in Excel are numbers that can be sorted, formatted or calculated in various ways using functions or ordinary formulas -- and they can be used to filter a database.
The only drawback is that dates prior to January 1, 1900 (in the 1900 date system) or prior to January 2, 1904 (in the 1904 date system) can't be entered into Excel as dates. They have to be entered as text and can't be used in numeric formulas or date functions... unless the old dates are converted to dates that Excel will recognize as dates.
First, a little explanation... Leap years are evenly divisible by four, unless they are century years, e.g. 1900, in which case they must be evenly divisibly by 400. That means the calendar (including which days fall on which dates) repeats itself every 400 years. So...
Press Ctrl+Home on your test worksheet and enter May 9, 1611 as 09/05/1611 (Australian date format) or 05/09/1611 (US date format) into cell C1. Although that cell entry looks like a date to you, to Excel it is text.
Next, if dates in your computer are set to dd/mm/yyyy format, copy the first formula below into cell D1, but if you use the mm/dd/yyyy date format, copy the second formula below into cell D1...
=DATE (VALUE(RIGHT(C1,4))+400,VALUE(MID(C1,4,2)),VALUE(LEFT(C1,2)))
=DATE (VALUE(RIGHT(C1,4))+400,VALUE(LEFT(C1,2)),VALUE(MID(C1,4,2)))
That will give you a date that is 400 years after the 1611 date -- and that is a valid Excel date that can be used in calculations etc. Alternatively, if you just want to see what day of the week May 9, 1611 was, and if you use Excel 2007 or later, download the free Excel file named Perpetual Diary and Calendar.xlsx from the near the bottom of the Free Downloads page of the web site (link below). It contains a calendar that goes from year 1066 to year 3000.
James F. Pearson (Jim) is the author of the JP Excel Training Files, a complete guide to using Excel, written in Excel work books. Click on the link below for more information or to download a copy of the JP Excel Training Files.
Special Offer... Get two for the price of one. Click on this link Learn Excel and purchase a copy of the JP Excel Training Files today. Use the emailed link you receive to download your copy then forward the email to a friend. They can use the link to download a copy too... for free.
About the Author
Creating a variable date on a DOT file in Microsoft Word 2000?
I want to make a DOT file in Microsoft Word 2000 of a template with a standard header. This header has to include the date, but if I write today's date, it will say that every day. How can I make it so that the date will change? Example: Today is February 3, 2008...if I type that in, on September 27, 2008, if I were to use the template, it would still say February 3, 2008. I would want it to say September 27, 2008. Is this possible? If not, is there another way around it?
This should work even if the file gets copied to another machine and gets a new "creation" date: (works for Word XP (2002))
1. Click the View->Headers and Footers menu item.
2. Position the cursor in the header where you want the date.
3. Click the Insert->Field menu item and select Date from the list box.
4. Select the format you want to use.
5. Click OK.
Now to make the date unchangeable.
6. Double click in the date to select the field.
7. Press CTRL-SHIFT-F9. This changes the field to regular text.
Hope that helps.
Today's Date Microsoft Word
Taming the Date Field in Word 2007
Tracking Credit Card Spending With Microsoft Money
First, a credit card tracking reality check
You may not need to use Money for tracking a credit card. You only need to use Money for credit-card record keeping if it is important to you to track your credit card balance and to track what you spend or charge at various merchants. This might seem
like something everybody would want to do, but if you always pay off your credit card balance at the end of each month and don’t need to be concerned about your balance between payments, you won’t need to track the outstanding balance. And if you don’t care where you charge, there’s no compelling reason for you to track individual credit card transactions.
Note: If you use Money to track a business credit card and you're organized as a C corporation, an S corporation, a partnership, or a limited liability company that's treated as a C corporation, an S corporation or a partnership, you may need to track credit balances if you include balance sheets with your corporation or partnership tax return.
Setting up a credit card account
If you decide to set up a credit card account, you can easily do so.
If you used the Setup Assistant to prepare for using Money, you may have already set up a credit card account. Money, during the setup process, would have asked you about credit card accounts that you hold and whether you want to set them up for record keeping. If you answered questions about your credit cards during the setup process, you’ve already set up credit card accounts and can begin using them.
If you didn’t set up credit card accounts as part of the Money setup process, you can easily do so by following these steps:
1. Display the Pick An Account To Use window.
Click the Accounts & Bills link, and then select Account List. Money displays the Pick An Account To Use window.
2. Indicate that you want to set up a new account.
Click Set Up Your Accounts in the list of common tasks.
3. Follow the New Account Wizard’s instructions.
To set up a new account, click Add A New Account to start the New Account Wizard. To set up your account, simply answer the wizard’s questions.
NOTE You need to set up a separate credit card account for each credit card you want
to track using Money.
Recording a credit card charge
To record credit card charges using Money, pick the credit card account you used and then describe the charge in detail, including the payee, the check date, and the amount.
To record charges, take the following steps:
1. Display the appropriate account register.
To display the register for the credit card account you used for the charge, click the account name listed in the Favorite Accounts area of the Money home page. If you don’t see the account listed, click the Accounts & Bills link and then select Account List to display a list of all your Money accounts. Then click the account you want.
2. Indicate that you want to record a charge.
To tell Money to record a charge, first click the Charge tab in the transaction entry area beneath the account register. Then click the New button. Money opens up input boxes, or fields, in the transaction entry area that you use to record information
about the charge.
3. (Optional) Provide the charge number.
If you have a number that uniquely identifies the charge, enter this information in the Number box.
4. Provide the charge date.
Select the Date box by either pressing the Tab key or clicking the mouse and then entering the charge date. Type a date using the regular date format, or click the box’s button to display a pop-up calendar that you can use to select the date. To use the pop-up calendar, click the day you want to enter. Although the calendar displays only one month’s worth of dates, click the arrows that appear on either side of the month name to move to the previous or upcoming month.
NOTE Money also provides several codes that you can use to enter dates once the Date box is selected. Type the letter t to tell Money to enter today’s date in the Date field. Type m to enter the first day or h to enter the last day in the current month in the Date box. Type y to enter the first day of the year or r to enter the last
day of the year in the Date box. Notice that t is the first letter in the word today, that m and h are the first and last letters in the word month, and that y and r are the first and last letters in the word year. This bit of trivia should help you remember these codes.
5. Identify the merchant or business you are paying.
Press the Tab key or click the mouse to select the Pay To box. Then enter the name of the person or business you are paying. Money keeps a list of all the people or businesses to whom you have previously written checks or with whom you’ve
charged money on a credit card. To select a name from this list, click the Pay To box’s button so that Money displays a list of past payees. Then select a name from the list by clicking.
6. Record the charge amount.
Press the Tab key or click the mouse to select the Amount box. Then enter the charge amount. Or click the Amount box’s button to display a pop-up calculator you can use to calculate the charge amount.
7. Categorize the charge.
To categorize the charge, press the Tab key or click the mouse to select the Category box. Next, click the Category box’s button so that Money displays a list of categories you can use to describe the reason for your charge. Then click the category you want.
You can also record a subcategory for the charge. To do this, press the Tab key or click the mouse to select the Subcategory box. Next, click this box’s button so that Money again displays a list—this time a list of subcategories. Then click the subcategory you want to select.
NOTE The Subcategory box appears just to the right of the Category box.
8. (Optional) Record a memo description.
You don’t need to record a memo description for credit card charges, but Money provides space for you to include additional information about a charge. If you want to collect this information—and it’s not a bad idea—press the Tab key or click the mouse to select the Memo box. Then enter any additional information you want to store in the field.
9. Record the charge.
After you have correctly described the charge, click the Enter button. This tells Money to record the charge in the register. As soon as Money records the charge in your register, it also updates the account balance.
About the Author
L. Nelson is the author of do it yourself kits for Incorporating in Arizona, Arizona S corporation, and Arizona limited liability company.
Today's Date Microsoft Word
