MeadInKent
| Format codes | Feedback | DateDif ages

Date and Time Functions

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 but some confusion can arise because of how the results are displayed - i.e. how your dates are formatted.

Would you like to learn more?

book

Excel 2010 Formulas by John Walkenbach (with CD)

If you type a date or time (e.g. 23/1/03 or 11:45) directly into a cell, Excel should recognise it as such. The program will normally align the value to the right of the cell and display it in the formula bar in a standard format (e.g. 23/01/2003 or 11:45:00). The NOW() function will display the current time or date in a cell. To derive the date from values in cells use the DATE(year, month, day) function.

Displayed value Formatting characters
24/05/2007 19:07 dd/mm/yyyy hh:mm
May 24, 2007 mmmm d, yyyy
7:07:04 PM h:mm:ss AM/PM
39226.7965761574 (none)

The four displayed values in the first example all reflect exactly the same value but display it differently. The displayed formats in particular cells can be modified using [Home] Number | Format Cells | Number | ribbon option. You can either choose a date format from the Category box or select Custom to define your own format picture. Regardless of how the date / time is displayed, the actual value stored in the cell is a long numeric value such as 37635.78.

time value alone 0.796 =MOD(NOW(), 1)
date value alone 39226 =INT(NOW())

The numeric values represent the number of days since 1st January 1900 and the time is the decimal fraction element (time divided by 24 e.g. 6pm = 0.75). If for a calculation you should wish to derive either solely the date or the time, formulae using INT() or MOD() will allow this.


Excel might produce unexpected results when calculating times which are on subsequent days (eg from 19:15pm to 06:30am). Find out how to avoid this.


- I J K L
19 Difference between 2 times or dates  
20   formatted decimal formula
21 time 1 11:45 0.49 (value)
22 time 2 10:15 0.427 (value)
23 difference 1:30 0.0625 [J23] =J21-J22
24        
25   formatted numeric formula
26 date 1 16/12/2002 37606 (value)
27 date 2 14/11/2002 37574 (value)
28 difference (days) 32 32 [J28] =J26-J27

The dates are stored as numeric values and therefore it is simple to perform calculations upon them. Addition and subtraction can be done where to add 1 is to add a day to a value.

- N O P
2 The Year, Month and Day elements of a date
3      
4   result formula
5 Now 24/05/07 19:07 =NOW()
6 A year 2007 =YEAR(O5)
7 A month 5 =MONTH(O5)
8 A day 24 =DAY(O5)
9 A date 24/05/2007 =DATE(O6, O7, O8)

The year, month or day element of a date can be derived from applying functions to a date value. There are also similar time functions: HOUR, MINUTE, SECOND


The DATEDIF() function allows you to calculate the exact ages between two dates. This will enable you to display someones precise age and it can be incorporated in a long formula to give a result such as: 'Your age is 23 years, 4 months and 6 days'. Click here to find out how.


In accounting applications it is often necessary to convert calendar months into financial months or periods. The financial year for an organisation may start on 1 April and so April is month 1, May is month 2 and so on. It is possible to calculate financial periods but it may be simpler to use the CHOOSE function as in these examples.

help
- R S T
15 Financial Periods    
16      
17 A date 20/06/2003 [S17] 37792
18 Calendar month 6 [S18] =MONTH(S17)
19 Financial periods 3 [S19] =CHOOSE(S18, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9)
20 Financial period name Jun [S20] =CHOOSE(S19, "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Jan", "Feb", "Mar")

To calculate the date of the first day of any particular month is easy. Determining the date of the first Monday (or any other weekday) is harder. The WEEKDAY function returns a number to represent the day of the week of a date. The second parameter allows you to modify the result values: 1 = [1 Sunday to 7 Saturday]; 2 = [1 Monday to 7 Sunday]; 3 = [0 Monday to 6 Sunday].

- C D E F
14 Particular days of the month   formatted date formula
15 A date 19/08/2003 Tuesday 19 Aug (value)
16 Date of first day of the month 01/08/2003 Friday 1 Aug [D16] =DATE(YEAR(D15), MONTH(D15), 1)
17 Date of first Monday in month 04/08/2003 Monday 4 Aug [D17] =IF(WEEKDAY(D16, 3)>0, 7-WEEKDAY(D16, 3), 0)+D16
18 Date of the last day of month 31/08/2003 Sunday 31 Aug [D18] =DATE(YEAR(D15), MONTH(D15)+1, 1)-1

The default format of dates is set by the Windows Regional Settings (Control Panel) - not within Excel or Access. This determines whether a US style (MM/dd/yy) or a British style (dd/MM/yy) is displayed when no other formatting information is specified. On this page, the default settings are British.

Excel can calculate the extra enhancements paid to staff working shifts at unsocial times such as nights and weekends. Find out how to do this.


Get this information as a document
accompanied by Excel worksheets
PDF functions guide Click here for details about
obtaining this file

file: datesandtimes.htm meadinkent.co.uk 2014 Page last updated Jan14 CMIDX S4 P1 Y