Excel Date & Time Calculations

Excel Date & Time Calculation/Excel Date & Time Formulas/Function

Got any Excel/VBA Questions? Free Excel Help

Excel Time and Date Calculations

If we wish to use these real time values in other calculations there are a few 'magic' numbers to keep in mind.

60 (Sixty Minutes)
60 (Sixty Seconds)
3600 (60 Secs * 60 Mins)
24 (Twenty Four Hours)
1440 (60 Mins * 24 Hours)
86400 (24 Hours * 60 Mins * 60 Secs)

Once we are armed with these magic numbers and the information above, the manipulation of times and dates is no longer a problem. That is of course providing your dates and/or times are valid.

Entering Valid Excel Dates & Times

To enter a valid date in Excel, use a slash mark or a hyphen to separate the parts of a date; for example, type 9/5/2002 or 5-Sep-2002. If you leave off the year portion (9/5), Excel will default it to the current year.

Tip: To enter the current date, press CTRL+; (semicolon)

To enter a time that is based on the 12-hour clock, type a space, and then type "a" or "p" after the time; for example, 9:00 p. Otherwise, Excel enters the time as AM. To Enter a time based on a 24-hour clock (military time) enter as 21:00, 11:00 etc.

Tip: To enter the current time, press CTRL+SHIFT+; (semicolon).

Adding/Subtracting Excel Dates & Times.

As Excel Dates & Times are seen as Serial Values (Dates) and Decimal Fractions (Times) we can easily add or subtract them like below;

=A1-A2

=A1+A2 or =SUM(A1:A2)

When subtracting dates we naturally want to subtract the lesser date from the greater date. If you are unsure which cell will house which you can use;

=MAX(A1:A2)-Min(A1:A2)

Subtracting Times That Span Past Midnight Into a New Day

Let's say A1 houses the Start time of 6:00PM and A2 the end time of 2:00AM. If we need to work out the hours worked between 6:00PM of 1 day and 2:00AM of the next day we cannot simply use;

=A2-A1

As Excel does not like negative times

.Instead, we should use;

=A2+(A1>A2)-A1

This will then add 1 (1 day) to A2 if the time in A1 is of a higher value than the time in A2. The expression (A1>A2) will equate to either TRUE or FALSE. TRUE has a value of 1 and FALSE a value of zero.

Add/Subtract Days to a Date

To add/subtract, say 2 days to a date, we can simply use;

=A1+2

=A1-2

Calculate the Difference Between 2 Dates

To find out the difference in 2 dates use:

=DATEDIF(A1,A2,"d")

Where A1 houses the earliest date. This will result in the number of days between 2 dates.

If, when working with dates and times we cannot know in advance which date or time is the earliest we can use the MIN and MAX functions, for example we could use:

=DATEDIF(MIN(A1:A2),MAX(A1:A2),"d")

The syntax for the DATEDIF function is;

DATEDIF(Start _Date,End_Date,Unit)

Valid Units are any one of the formats below

"M", "D", "Y", "YM" (The months and years of both dates are ignored) ,"YD" (The days and years of both dates are ignored) and "MD" (The years of both dates are ignored).

Convert From Decimal Time

If you have the number 5.50 and you really want 5:30 or 5:30AM use:

=A1/24 and format as needed.

If it should be 17:00 or 5:30PM use:

=(A1/24)+0.5

Convert To Decimal Time

To get the opposite, that is a decimal time from a true time, use

=A1*24

Extract Date Only From Date & Time

If a cell has both the true date and true time (e.g. 22/Jan/02 15:36) and we only want the Date, use:

=INT(A1)

Extract Time Only From Date & Time

To get the time only use:

=MOD(A1,1) and format as needed.

Negative Times

By default, excel cannot calculate or show true negative times, instead you will see ############. This can be overcome by switching Excel to the 1904 Date System via Tools>Options - Calculations and check the 1904 Date System checkbox. The 1904 date system changes the Starting date from which all dates are calculated from January 1, 1900, to January 2, 1904. This is VERY IMPORTANT as, if you are using other Date calculations in the same Workbook they will end up returning erroneous results.

Add/Subtract Months or Years to a Date

We can also easily tell Excel to add to any date any amount of days, months or years. Here is how:

=DATE(YEAR(A1)+value1,MONTH(A1)+value2,DAY(A1)+value3)

So to add 1 month to a date in cell A1 we could use:

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

to add 1 year to a date in cell A1 we could use:

=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))

There are however some other Date and Time functions Excel has that are part of the Analysis ToolPak Click Add-Ins on the Tools menu. Click to select the Analysis ToolPak check box, and then click Yes if you are asked if you want to install it.

You will then have functions such as:

EDATE
EOMONTH
NETWORKDAYS
WEEKNUM

All of these will be found under the Date & Time category of the Paste Function dialog, Function Wizard. These are very easy to use and the help in Excel explains these very well.

Gallery

stars (0 Reviews)

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
OzGrid is in no way associated with Microsoft.

Website designed by Astrava Solutions Ltd.