| | Format codes | Feedback | DateDif ages | | ||
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.
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 large 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.
| - | 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 |
 |
Click here for details about
obtaining this file. It has been rewritten for Excel 2010. |
| file: datesandtimes.htm | © meadinkent.co.uk 2016 | Page last updated Jan14 | CMIDX S4 P1 Y |