Fundamentals of working with dates and times in Excel

Video

As usual, who needs to quickly – watch the video. Details and nuances – in the text below:

How to enter dates and times in Excel

If we keep in mind the regional settings, then Excel allows you to enter the date in very different ways – and understands them all:

The appearance (display) of a date in a cell can be very different (with or without a year, a month as a number or a word, etc.) and is set through the context menu – right-click on the cell and then Cell format (Format Cells):

Time is entered into cells using colons. For example

16:45

If desired, you can additionally specify the number of seconds – entering them also separated by a colon:

16:45:30

And, finally, no one forbids specifying the date and time at once together through a space, that is 

27.10.2012 16:45

Quick entry of dates and times

To enter today’s date in the current cell, you can use the keyboard shortcut Ctrl + Ж (or CTRL+SHIFT+4 if you have a different default system language).

If you copy a cell with a date (drag from the lower right corner of the cell), holding right mouse button, you can choose how to copy the selected date:

If you often have to enter different dates in the cells of the sheet, then it is much more convenient to do this using a pop-up calendar:

If you want the cell to always contain the actual today’s date, it is better to use the function TODAY (TODAY):

How Excel actually stores and processes dates and times

If you select a cell with a date and set for it General format (right click on cell Cell format – tab Number — General), you can see an interesting picture:

That is, from the point of view of Excel, 27.10.2012/15/42 41209,65417:XNUMX pm = XNUMX

In fact, Excel stores and processes any date exactly like this – as a number with an integer and a fractional part. The integer part of the number (41209) is the number of days that have passed since January 1, 1900 (taken as a reference point) until the current date. And the fractional part (0,65417), respectively, the share of the day (1 day = 1,0)

From all these facts two purely practical conclusions follow:

• Firstly, Excel cannot work (without additional settings) with dates earlier than January 1, 1900. But we will survive this! 😉
• Secondly, it is possible to perform any mathematical operations with dates and times in Excel. Precisely because they are actually numbers! But this already opens up a lot of opportunities for the user.

Number of days between two dates

It is considered a simple subtraction – we subtract the initial date from the end date and translate the result into General (General) number format to show difference in days:

Number of business days between two dates

Here the situation is a little more complicated. Saturdays, Sundays and holidays must not be taken into account. For such a calculation, it is better to use the function PURE WORKERS (NETWORKDAYS) from category Date and Time. As arguments to this function, you must specify the start and end dates and cells with weekend dates (public holidays, sick days, vacations, days off, etc.):

Note: This function has appeared in the standard set of Excel functions since the 2007 version. In older versions, you must first connect the add-on The analysis package. To do this, go to the menu Service – Add-ons (Tools — Add-Ins) and check the box next to Analysis package (Analisys Toolpak). After that, in the Function Wizard in the category Date and Time the function we need will appear PURE WORKERS (NETWORKDAYS).

The number of complete years, months, and days between dates. Age in years. Experience.

About how to calculate it correctly, it is better to read here.

Shift the date by a specified number of days

Since one day in the Excel date reference system is taken as a unit (see above), to calculate a date that is, say, 20 days away from the given one, it is enough to add this number to the date.

Shift the date by a specified number of business days

This operation is performed by the function WORKDAY (WORKDAY). It allows you to calculate a date that is forward or backward relative to the start date by the desired number of working days (taking into account Saturdays and Sundays and public holidays). Using this function is exactly the same as using the function PURE WORKERS (NETWORKDAYS) described above.

Calculating the day of the week

Weren’t you born on Monday? Not? Sure? It can be easily checked with the function DAY (WEEKDAY)from category Date and Time.

The first argument of this function is a cell with a date, the second is the type of counting days of the week (the most convenient is 2).  

Calculation of time intervals

Since time in Excel, as mentioned above, is the same number as a date, but only its fractional part, then any mathematical operations are also possible with time, as with a date – addition, subtraction, etc.

There is only one nuance here. If, when adding several time intervals, the sum turned out to be more than 24 hours, then Excel will reset it and start summing again from zero. To prevent this from happening, you need to apply the format to the final cell 37:30:55:

• How to calculate age (experience) in full years-months-days
• How to make a drop-down calendar to quickly enter any date in any cell.
• Automatically add the current date to a cell when entering data.
• How to calculate the date of the second Sunday in February 2007, etc.