Copyright infringement

How to extract the date and time from a serial date in Excel

If you have to work with a date stamp in Microsoft Excel that includes the date and time, you can use these simple expressions to extract both components, making it easier to work with.

Table

Photo: Rawpixel Ltd, Getty Images/iStockphoto

Working with dates and times is a common need in an Excel sheet. If you’re lucky, the structure supports the way you use the paper. Sometimes we inherit a sheet, or we have to fulfill a new request. In short, a date serial value can contain only date and time or date or time. Ultimately, you may need to know how to extract the date and time from the serial value that combines the two. In this article, I’ll show you a simple function, TRUNC() that can do both.

We see: 69 Excel Tips Every User Should Master (TechRepublic)

I’m using Microsoft 365 on Windows 10 64-bit, but you can use earlier versions. You can work with your own data or download the .xlsx and . xls. The browser version supports the expressions in this article.

About date calculation in Excel

Unless you’re new to Excel, you’re probably familiar with the way Excel stores dates and times using sequential values. This section is a short review.

The dates usually look the way you expect; For example, April 6, 2021 or 4/6/2021. There are other date formats that you will also recognize. With the specified date and time formats, you can display the date or time in several ways. However, that’s not how Excel stores the date – and it’s not the formatted string you see. Instead, Excel stores the value as a series The value where 1 January 1900, has a date serial value of 1 and adds 1 for each day moving forward in time. If you want to do the math, you will find that the date serial value for April 6, 2021 is 44292.

We see: Windows 10: Voice Command Menus for Speech Recognition and Dictation (Free PDF) (TechRepublic)

To see this for yourself, enter the date April 6, 2021. Excel will display it as shown in Figure A Because this is the default date format. Don’t worry if you see something a little different because you’re probably working with a different default (if someone changed it). To the right the same date is entered in the same way, but the format is different; I changed it to public so it can store the serial value. When you enter a date string, Excel formats it as a date using the date format, d-mmmm-yy.

Figure A

exceldate-a.jpg

These two entries are stored the same way in Excel.

The date is an integer. Time is a decimal value. A day equals 24 hours. Twelve-hour half-day, or 0.50. For comprehensive details, see Table A. In the following sections, you’ll work with full sequential values ​​- dates that include the date and time.

Table A

h: mm: ss . format

Serial time

equivalent time

23:59:59

0.999988426

23 hours 59 minutes 59 seconds

1:00:00

0.041666667

1 hour

23:00:00

0.958333333

23 hours

0:01:00

0.000694444

1 minute

0:59:00

0.040972222

1 minute 59 seconds

0:00:01

1.15741E-05

1 second

0:00:59

0.00068287

59 seconds

How to extract the date in Excel

You will often work with date values ​​that have no decimal or time values. When the two are combined into a single value, such as 44292.0412, it can be difficult to work with them. For example, 44292.0412, 44292.0413 and 44292,000695 are not the same value, even though they share the same date. Figure B Displays three grouped sequential values. Again, I entered dates as literal date strings in the first column. To expose the serial values, I applied the generic format to column C.

Figure B

exceldate-b.jpg

Column D contains the simple function

= TRUNC (B3)

Copy it to the remaining cells. As you can see, Excel applies the global formatting automatically and returns only the integer part of the serial value grouped in column B. You might be wondering why I didn’t use INT(). You can, but TRUNC() returns only integer numbers; INT() rounds a number down to the nearest integer. Since we’re working with positive date values, it doesn’t matter, but negative dates are possible, so TRUNC() is the safer way.

How to extract time in Excel

Dealing with time is often more difficult than dealing with dates. However, in this case, it is almost as simple as using the TRUNC() function. Figure C Shows the result of using the simple expression

= B3-TRUNC (B3)

And copy it to the remaining cells. In short, this expression subtracts the integer part (the date) from the summed serial value. For example, the expression in E3 is evaluated as:

= B3-TRUNC (B3)

=44292.375-TRUNC(44292.375)

= 44292.375-44292

.375

Figure C

exceldate-c.jpg

Use this simple expression to extract the time.

MOD() can be used, but TRUNC() evaluates only the exact components: integer and decimal. At this point you can see the serial values ​​representing the date and time, but you don’t see the actual date and time strings that work for you.

How to add formatting

Serial values ​​don’t make sense to the viewer, so let’s format columns C and D, so they show something meaningful. First, select D3: D5, right-click on the selection and choose Format Cells from the resulting submenu. In the resulting dialog, choose Date from the Category list. (You can also use the Format dropdown on the Numbers group.) The type preview will update according to the examples. Choose the format that best suits your needs, such as Wednesday, March 14, 2021, as shown in figure d. Next, select E3:E5, right-click, choose Format Cells and choose Time from the Category menu. Choose 1:30:55 PM from the genre list.

figure d

exceldate-d.jpg

Choose the date format.

Figure E Shows the results of the formatting of both columns for the extracted sequential values. The formatted values ​​exactly match the grouped sequential values. We didn’t lose or add anything. All we did was extract the integers and decimal values ​​from the sequential values ​​in column B

Figure E

exceldate-e.jpg

Serial values ​​extraction format.

see also