Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Data Series in Excel
  • Understanding Pivot Tables in Excel
  • How to Create Gantt Chart in Excel
  • Create Scatter Chart in Excel
  • Subtotal function in Excel

References

  • Count rows that contain specific values in Excel
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Excel Advanced Lookup using Index and Match Functions
  • Extract all partial matches in Excel
  • INDEX function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Excel Data validation must begin with
  • Excel Data validation with conditional list
  • Excel Data validation date in next 30 days
  • Excel Data validation unique values only
  • Excel Data validation exists in list

First in, last out times in Excel

by

This tutorial shows how to calculate First in, last out times in Excel using the example below;

Formula

=MINIFS(times,names,name,actions,action)

>

Explanation

To get “first in, last out” times from timesheet data, you can use the MAXIFS and MINIFS functions, or an array formula as described below. In the example shown, the formula in H6 is:

=MINIFS(times,names,H5,actions,"in")

with the following named ranges: actions = C5:C22. names = B5:B22, times = E5:E22

Worked Example:   MAXIFS function: Description, Usage, Syntax, Examples and Explanation

How this formula works

The MINIFS function is designed to return minimum values in a range based on one or more criteria. In this case, the range we are extracting a minimum value from is E5:E22), named “times”.

We are filtering these values first using two range/criteria pairs. The first pair provides a range of B5:B22 (names), with the name in H5 for criteria. The second pair uses the range C5:C22 (actions), with “in” as a criteria.

Worked Example:   Maximum value if in Excel

MINIFS then returns the earliest (minimum) time where name is “Max” and action is “in”.

The “last out” time is calculated in a similar way with the MAXIFS function:

=MAXIFS(times,names,H5,actions,"out")

Array formula alternatives

The MAXIFS function and MINIFS function were both introduced in Excel 2016. If you are using an older version of Excel, you can use an array formula instead:

{=MIN(IF(names=K5,IF(actions="in",times)))}
{=MAX(IF(names=K5,IF(actions="out",times)))}

Note: these are array formulas and must be entered with control + shift + enter. Excel will add the curly braces automatically.

Worked Example:   Minimum value if in Excel

Post navigation

Previous Post:

Weighted average in Excel

Next Post:

Win loss points calculation in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Extract multiple matches into separate rows in Excel
  • How to return blank in place of #DIV/0! error in Excel
  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • Return blank if in Excel
  • How to use Excel AND Function

Date Time

  • Get days, hours, and minutes between dates in Excel
  • How to enter Today’s Date or Static Date and Time in Excel
  • How to calculate next day of week in Excel
  • How to get workdays between dates in Excel
  • TIME function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • If cell contains one of many things in Excel
  • Group times into 3 hour buckets in Excel
  • How to randomly assign people to groups in Excel
  • Categorize text with keywords in Excel
  • How to randomly assign data to groups in Excel

General

  • Zoom Worksheet in Excel
  • How to Insert Cells, Row and Rows in Excel
  • Spell Check in Excel
  • How to calculate percent change in Excel
  • How to get original price from percentage discount in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning