Excel Date & Time Functions and Formulas

6137494695 
Tips and Ideas on Excel 
Tips and Ideas on Macros 
Excel Chapter 15 of 24: Excel Date & Time Functions and FormulasThe three most important things that you should remember when working with dates and times are: For example: Let's say that you have the dates 1/16/2005 in cell A1 and 1/22/2005 in cell B1  6 if the format of cell C1 is either "General" or "Number" Using the proper formats you might want to work dates even without date & Time functions: If you have a date in cell A1 and you want the date for the next day in cell B1 (formatted "date" ) the formula will be: 
Excel offers you 20 functions in the date & time category. See them all with a short description in Excel Spreadsheets Date and Time Functions (chapter 24). Here are the functions (12) that you will use more often with tips and examples. 

Functions  What it Does 
DATE  Returns the serial number of a particular date 
DAY  Converts a serial number to a day of the month 
HOUR  Converts a serial number to an hour 
MINUTE  Converts a serial number to a minute 
MONTH  Converts a serial number to a month 
NOW  Returns the serial number of the current date and time 
SECOND  Converts a serial number to a second 
TIME  Returns the serial number of a particular time 
TODAY  Returns the serial number of today's date 
WEEKDAY  Converts a serial number to a day of the week 
YEAR  Converts a serial number to a year 
DATEDIF 
Calculates the interval between two dates 
Examples of basic Excel Date and Time Formulas DATEDIF One note to start. If you go to the menu " Insert/Function" you won't find this function. Excel has forgotten it. Here is how it works. Let's say that you have the dates 1/16/2005 in cell A1 and 1/16/2006 in cell B1: DAY, MONTH, YEAR With a date in A1 like 12/15/2005 the formulas =DAY(A1), =MONTH(A1) and =YEAR(A1) will return 15, 12 and 2005. SECOND, MINUTE, HOUR With a TIME in A1 like 1:31:45PM the formulas =SECOND(A1), =MINUTE(A1) and =HOUR(A1) will return 1, 31 and 45. WEEKDAY If the date in A1 is 1/16/2006 and it is a Monday the formula =WEEKDAY(A1) will return 2. For most users day 1 is Sunday. Check what your system says because in some cases day 1 is Monday. DATE, DAY, MONTH, YEAR With the DATE function, the arguments are always in the following order (year,month,day) whatever the date format specified in your regional parameters. With a date in cell A1 MONTH, DAY, NOW, AND and IF (Anniversary Alerts) My client wanted a spreadsheet that would tell her when it is the birthday of an employee. We created a spreadsheet with the names in column A and the dates of birth in column B. In column D was this formula =NOW() that changes date each day. In column C we put this formula: We fine tuned: Copy/paste the formula above in your spreadsheet. If you want to be alerted more that a week before the anniversary change the 7 for 30 in the formula. This way you will be alerted a month in advance. 
Next Chapter: Financial Functions and Formulas in Excel 
Excel Table of Contents 
Discover Even More in 50 Excel spreadsheets 

6137494695 
Tips and Ideas on Excel 
Tips and Ideas on Macros 
Excel Date & Time Functions and Formulas