How to return first and last times from timestamps in Microsoft Excel


Microsoft office excel application menu
Image: PixieMe/Adobe Stock

If you have a call center and track incoming callers and their call times, you might store all of that information in Microsoft Excel. The sheet would store caller identification and timestamp values.

Reporting them though, won’t be as simple as printing off a list each day because callers may call several times throughout a single day. That’s what will be meaningful to those needing the information.

In this tutorial, you’ll learn what a timestamp is and then how to use Excel functions MIN() and MAX() to return the first and last call for the day from timestamps. Then, you’ll build a grouped set of records that returns the first and last call for each caller.

I’m using Microsoft 365 on a Windows 10 64-bit system. A few of the functions used are available only in Microsoft 365 and Excel for the web.

SEE: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)

How to return the first time value in Excel

A timestamp is a combined date and time that marks a specific point in time. If you change the cell’s format to General or Number, you will see a number instead of a date. The number’s integer represents the date, and the decimal value represents the time within that date.

For example, Figure A shows a column of timestamps formatted to display as dates and times. The column next to it shows the underlying values for each timestamp.

Figure A

Excel test data including several dates and numbers organized into two columns
Figure A: The integer is the date, and the decimal value is the time.

Now, let’s suppose your help desk call center tracks calls by caller and the time the call comes in. At the end of the day, you want to know the first and last calls of the day. The simple demonstration sheet shown in Figure B lists the calls in order, so it’s easy to see the first and last call, but that won’t always be the case, depending on how personnel enter the call records.

Figure B

Excel test date with cell 3F highlighted
Figure B: Personnel enter call records for each call into the help desk center.

It would be easy for one operator to enter a call record a few minutes later than received, and then your records would no longer be in order, by time. So, we’ll not count on that in our solution.

Fortunately, Excel’s MIN() function will return the earliest (smallest time value) call of the day. This simple function requires only one argument and that’s the range or structured reference that contains the values we’re evaluating.

The function

=MIN(TableCalls[Call Date])

uses structured reference because the data range is an Excel Table object named TableCalls. If you’re evaluating a normal data range, you’d use the reference

=MIN(C3:C12).

The function

=VLOOKUP(F3,TableCalls,1)

returns the client who made that first call, as shown in Figure C. The structured reference, TableCalls is the Table’s name. F3 refers to the first call time (to the right), and the argument 1 returns the corresponding value in the first column of TableCalls.

Figure C

Excel test data with cell 3E highlighted
Figure C: The XLOOKUP() function returns the client who made the first call of the day.

Now let’s return the last call of the day.

How to return the last time value in Excel

Once working through the functions to return the earliest call and the client making that call, doing the same for the day’s last call is simple. We’ll use MAX() to return the last call and another XLOOKUP() function to return the client making that call.

Figure D shows both functions:

E5: =VLOOKUP(F5,TableCalls,1)

F5: =MAX(TableCalls[Call Date])

Figure D

Excel test data that has cell 5E selected
Figure D: Both XLOOKUP() and MAX() functions are used.

The XLOOKUP() function returns the client that made the last call by finding the time value in F5 and returning the corresponding value from the Customer ID column. The MAX() function returns the latest (the largest time value) call from the time values in the Call Date column, C5:C12.

If you’re following along and the time values in F3 and F5 are displaying both the date and time, you can reformat those cells to display only the time.

First, select F3, and on the Home tab, click the Format dropdown in the Number group. Choose Time from the dropdown list. Repeat these steps for cell F5.

That was easy and works if all you need is the first and last call of the day. Let’s suppose you want a record for each client that returns the first and last call of the day if that client made more than one call. This requirement is more complex.

How to return a caller and their calls in one record in Excel

It’s possible that management might want to see a list of all clients with their first and last call if that client made more than one call. You won’t meet that request with a few simple functions, but you can do it (Figure E).

Figure E

Excel test data with the organized test data in column H highlighted
Figure E: This simple grouping returns the first and last call for each Customer ID.

The first step is to return a unique list of Customer IDs. To do so, enter the following dynamic array function into H3

=SORT(UNIQUE(TableCalls[Customer ID]))

This function returns a sorted unique list of Customer ID values as a dynamic array. That means there’s only one expression, and it’s in H3. The rest of the column is a spill range — the results needed to fulfill the expression.

To return the first call for each Customer ID, enter the following function into I3 and copy it to the remaining cells:

=XLOOKUP($H3,TableCalls[Customer ID],TableCalls[Call Date])

This function returns the first call for the corresponding Customer ID in column H.

To return the last call for each Customer ID, enter the following function into J3 and copy it to the remaining cells:

=XLOOKUP($H3,TableCalls[Customer ID],TableCalls[Call Date],”No Results”,,-1)

The last argument, -1, performs the search from the bottom up, which is why it can return the last call. If you sort your calls in descending order, you’ll need to modify both functions by removing it from the one in J3 and adding it to the one in I3.

This setup works, but only one customer called more than once, so the functions duplicate the first call as the last call. The result is worse than distracting, it’s confusing, so let’s add a conditional format that will hide duplicate times in the last call column.

First, select J3:J9, and on the Home tab, click Conditional Formatting in the Styles group and select New Rule from the dropdown. In the resulting dialog, click the last choice in the top pane, Use a Formula to Determine Which Cells to Format.

In the formula control, enter =$J3=$I3 (Figure F). Click Format, click the Font tab, choose white from the palette, and click OK twice to return to the sheet.

Figure F

Select a Rule Type menu open in Excel with Use a formula to determine which cells to format selected
Figure F: Enter a conditional format formula.

As you can see in Figure G, only the last time value for Customer ID 101 is visible. The other values are there, but you can’t see them because the font is the same color as the background. I don’t like to hide things, but because this will update on a routine basis, it seems a convenient solution.

Figure G

Excel data with conditional formatting so repeat data is not visible
Figure G: The conditional format displays the last call value when it doesn’t match the first call value.

Stay tuned

It seems like a lot of work, but all the functions we used are easy to implement. The one hiccup is that you can’t use dynamic array functions in Table objects. That means you must update the functions in columns I and J and the conditional formatting reference, when necessary. For that reason, I’ll show you how to accomplish the same thing with a PivotTable in a future article.

If you’re not familiar with XLOOKUP(), you can read How to use the newish XLOOKUP() dynamic array function in Excel. To learn more about UNIQUE(), read How to use the UNIQUE() function to return a count of unique values in Excel.



Source link

istanbul escort aksaray escort arnavutköy escort ataköy escort avcılar escort avcılar türbanlı escort avrupa yakası escort bağcılar escort bahçelievler escort bahçeşehir escort bakırköy escort başakşehir escort bayrampaşa escort beşiktaş escort beykent escort beylikdüzü escort beylikdüzü türbanlı escort beyoğlu escort büyükçekmece escort cevizlibağ escort çapa escort çatalca escort esenler escort esenyurt escort esenyurt türbanlı escort etiler escort eyüp escort fatih escort fındıkzade escort florya escort gaziosmanpaşa escort güneşli escort güngören escort halkalı escort ikitelli escort istanbul escort kağıthane escort kayaşehir escort küçükçekmece escort mecidiyeköy escort merter escort nişantaşı escort sarıyer escort sefaköy escort silivri escort sultangazi escort suriyeli escort şirinevler escort şişli escort taksim escort topkapı escort yenibosna escort zeytinburnu escort porno 1080p porno izle 4k porno izle 720p porno izle abella danger alman alman porno alman porno izle aloha tube porno amatör amatör porno amatör porno izle anal anal porno anal porno izle arap porno asa akira porno asyalı porno bangbros porno bangbros porno izle banyoda sikis başörtülü porno beeg porno izle beyaz tenli porno izle biseksuel porno izle bisexsuel porno brandi love porno brazzers brazzers porno izle canli porno canli porno izle çinli porno çinli porno izle ensest porno ensest porno izle ensest seks erotik porno erotik porno izle esmer porno esmer porno izle etek altı fake agent fake taxi fake taxi porno fantazi pornoları fantezi porno izle fetiş porno fetiş porno izle fetish fransız porno fransız porno izle full hd hg porno izle gangbang porno genç kız porno izle genç kız sikişi genç teen porno izle gizli çekim porno gizli çekim pornosu grup pornosu grup porno grup porno izle hd pornolar hd porno hd porno izle hemşire porno hemşire pornosu hizmetçi porno hizmetçi porno izle ingiliz porno japon pornoları japon porno kızlık bozma kızlık bozma porno izle konulu porno konulu porno izle koreli porno köylü pornoları kumral porno kumral porno izle latin pornoları latin porno latin porno izle lezbiyen pornoları lezbiyen porno lezbiyen porno izle lisa ann porno liseli pornoları liseli porno liseli porno izle manken porno manken porno izle masaj porno izle masturbasyon porno izle masturbasyon pornoları mature porno mia khalifa porno mia malkova porno milf porno izle mobil porno mobil porno izle öğrenci porno izle öğretmen porno izle okul porno izle olgun kadın pornosu olgun porno oral porno oral porno izle oral seks porna izle pornhub pornhub porno izle porno film izle porno indir porno izle porno resimler porno star porntube porno izle redtube redtube pornoları riley reid porno rokettube rus pornoları rus porno rus porno izle sakso blowjob porno izle sarışın pornoları sarışın porno sarışın porno izle sarışın pornoları sekreter porno shemale sikiş sikiş sikiş izle şişman porno siyahi pornoları suriyeli pornoları swinger porno tecavüz porno teen porn türbanlı pornoları türbanlı porno türk pornoları türk porno türk porno izle türkçe altyazılı porno türkçe altyazılı porno izle xhamster pornoları xhamster porno xhamster porno izle xnxx xnxx porno xnxx porno izle xvideos xvideos porno izle yaşlı porno yeşilçam porno izle youjizz youporn youporn porno izle zenci porno güvenilir bahis siteleri bahis siteleri casino deneme bonusu casino siteleri deneme bonusu para yatırma bonusu bahis siteleri casino siteleribahis sitesi para yatırma