If you need to work with a date stamp in Microsoft Excel that features date and time, you should use these easy expressions to extract each parts, making them simpler to work with.

Picture: Rawpixel Ltd, Getty Photographs/iStockphoto

Working with dates and occasions is a typical want in an Excel sheet. If you happen to’re fortunate, the construction helps the way in which you employ the sheet. Typically we inherit a sheet, or we’ve to accommodate a brand new request. In a nutshell, a date serial worth can comprise the date and time or solely the date or time. Finally, you would possibly have to know how one can extract the date and time from a serial worth that mixes each. On this article, I will present you a easy operate, TRUNC() that may do each.

SEE: 69 Excel suggestions each consumer ought to grasp (TechRepublic)

I am utilizing Microsoft 365 on a Home windows 10 64-bit system, however you should use earlier variations. You possibly can work with your individual information or obtain the demonstration .xlsx and .xls information. The browser version helps the expressions on this article.

About date arithmetic in Excel

Until you are new to Excel, you are most likely a bit conversant in the way in which Excel shops dates and occasions utilizing serial values. This part is a brief overview. 

Dates often look as you anticipate; for instance, April 6, 2021, or 4/6/2021. There are different date codecs that you will additionally acknowledge. Utilizing particular date and time codecs, you may show a date or time in some ways. That is not how Excel shops a date, although—not because the formatted string that you simply see. Somewhat, Excel shops the worth as a serial worth the place January 1, 1900, has a date serial worth of 1 and provides 1 for daily transferring ahead in time. If you happen to had been to do the maths, you’d discover that the date serial worth for April 6, 2021, is 44292.

SEE: Home windows 10: Lists of vocal instructions for speech recognition and dictation (free PDF) (TechRepublic)

To see this for your self, enter the date April 6, 2021. Excel will show it as proven in Determine A as a result of that is the default date format. Don’t fret when you see one thing a bit completely different since you is likely to be working with a special default (if somebody modified it). To the fitting is identical date entered the identical method, however the format is completely different; I modified it to Normal so you may the saved serial worth. If you enter a date string, Excel codecs it as a date utilizing the date format, d-mmm-yy.

Determine A

exceldate-a.jpg

  These two entries are saved the identical in Excel.

The date is an integer; time is a decimal worth. A day equals 24 hours. Twelve hours is half a day, or 0.50. For a complete breakdown, see Desk A. Within the following sections, you may be working with full serial values—dates that embody each a date and time.

Desk A

Format h:mm:ss

Time Serial

Time Equal

23:59:59

0.999988426

23 hours, 59 minutes, and 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 and 59 seconds

0:00:01

1.15741E-05

1 second

0:00:59

0.00068287

59 seconds

Methods to extract the date in Excel

You will usually work with date values that haven’t any decimal worth or time values. When the 2 are mixed into one worth, similar to 44292.0412, they are often tough to work with. For instance, 44292.0412, 44292.0413, and 44292.000695 aren’t the identical worth, regardless that they share the identical date. Determine B exhibits three mixed serial values. Once more, I entered the dates as literal date strings within the first column. To show the serial values, I utilized the Normal format to column C.

Determine B

exceldate-b.jpg

Column D incorporates the straightforward operate

=TRUNC(B3)

copied to the remaining cells. As you may see, Excel applies the Normal format routinely and returns solely the integer portion of the mixed serial worth in column B. You is likely to be questioning why I did not use INT(). You possibly can, however TRUNC() returns solely the integer digits; INT() rounds a quantity right down to the closest integer. As a result of we’re working with optimistic dates values, it will not matter, however destructive dates are potential, so TRUNC() is the safer route.

Methods to extract the time in Excel

Coping with time is usually tougher than coping with dates. Nonetheless, on this case, it is almost as easy utilizing the TRUNC() operate. Determine C exhibits the results of utilizing the straightforward expression

=B3-TRUNC(B3)

and copying it to the remaining cells. In a nutshell, this expression subtracts the integer portion (the date) from the mixed serial worth. For instance, the expression in E3 evaluates as follows:

=B3-TRUNC(B3)

=44292.375-TRUNC(44292.375)

=44292.375-44292

.375

Determine C

exceldate-c.jpg

  Use this easy expression to extract the time.

MOD() can be utilized, however TRUNC() evaluates solely the precise parts: the integer and the decimal. At this level, you may see the serial values that signify the date and time, however you do not see the precise date and time strings that make sense to you.

Methods to add the formatting

The serial values do not make sense to the viewer, so let’s format columns C and D, so that they show one thing significant. First, choose D3:D5, right-click the choice and select Format Cells from the ensuing submenu. Within the ensuing dialog, select Date from the Class record. (You can additionally use the Format dropdown within the Quantity group.) The Kind preview will replace accordingly with examples. Select the format that fits your wants, similar to Wednesday, March 14, 2021, as proven in Determine D. Subsequent, choose E3:E5, right-click, select Format Cells and select Time from the Class record. Select 1:30:55 PM from the Kind record.

Determine D

exceldate-d.jpg

   Select a date format.

Determine E exhibits the outcomes of formatting each columns of extracted serial values. The formatted values match completely to the mixed serial values. We did not lose or add a factor. All we did was extract the integer and decimal values from the serial values in column B

Determine E

exceldate-e.jpg

  Format the extract serial values. 

Additionally see

Source link