如何在Excel中的两个日期之间返回多个值? [英] How to return multiple values between two dates in excel?

问题描述

我们有一个包含所有员工的电子表格，我需要生成所有员工的清单，这些员工的3个月试用期即将在下周到期.

We have a spreadsheet of all our employees, and I need to generate a list of all employees who's 3 month probation period is about to expire in the coming week.

换句话说，我需要一个列表，该列表将自动显示所有员工记录，这些记录的3个月试用期将在接下来的7天内到期.它必须是一个数组，因为每周会有多条记录，并且每天都会更改.

In other words, I need a list that will automatically display all employees records who's 3 month probation period is about to expire in the next 7 days. It needs to be an array because there will be multiple records each week, and they will change every day.

A列是他们的ID号，这是我需要返回的值.

Column A is their ID number, which is the value I need to return.

我们的数据如下所示:

A       B           C
ID      Name        Hire Date
1234    Joe Blow    February 21, 2014
2345    Man Chu     February 26, 2014
3456    Jim Hill    February 26, 2014
4567    Brad Chill  February 28, 2014
5678    Mike Grow   March 5, 2014
6789    Hibs Bee    March 5, 2014
1230    Sarah Mean  March 7, 2014

我已经尝试过像这样的Index& Match` {== INDEX($ A:$ A，SMALL(IF(AND($ C:$ C> ="&(today()-90)，$ C:$ C< ="&(today()-80))，ROW($ C:$ C))，ROW(1:1))，1)}以及其他一些公式，但不是工作，我不知道为什么.

I've tried Index&Match like this `{=INDEX($A:$A,SMALL(IF(AND($C:$C">="&(today()-90),$C:$C"<="&(today()-80)),ROW($C:$C)),ROW(1:1)),1)} as well as some other formulas, but it's not working, and I can't figure out why.

任何帮助将不胜感激，非常感谢.这让我发疯了！

Any help would be appreciated, thanks very much. This is driving me crazy!

推荐答案

您的注释和公式表明您想要找到雇用日期在80到90天(含)之间的员工.90天不一定总是三个月，但是如果有问题，可以随时更改功能.

Your comments and formulas indicate you want to find employees whose hire date was between 80 and 90 days ago, inclusive. 90 days will not always be three months but if that is an issue, you can always change the function.

以下输入的数组公式将返回其雇用日期符合这些规定的员工ID列表(与H1中的日期相比).在某些单元格中输入公式，然后尽可能地填写.当不再有满足规格的公式时，该公式将返回空白.该公式假定ID列在A列中，并且第一个条目在A2中(在A1中为标题)，如您在示例中所示.

The following array entered formula will return a list of the employee ID's whose hiredate meets those specifications compared with a date in H1. Enter the formula in some cell and fill down as far as might be required. The formula will return blanks when there are no more meeting the specifications. The formula assumes the ID column is column A, and that the first entry is in A2 (header in A1) as you show in your example.

HireDate和ID是指的范围，在这种情况下，是指A2:A8和C2:C8

HireDate and ID are named ranges referring, in this case to A2:A8 and C2:C8

=IFERROR(INDEX($A:$A,SMALL(((HireDate+80)<=$H$1)*((HireDate+90)>=$H$1)*ROW(ID),SUM(N(((HireDate+80)<=$H$1)*((HireDate+90)>=$H$1)=0))+ROWS($1:1))),"")

此公式必须输入数组:

要数组输入公式将公式放到单元格或公式栏中，按住按下输入时 Ctrl移.如果你这样做正确，Excel会在公式周围用大括号 {...} .

To array-enter a formula, after entering the formula into the cell or formula bar, hold down ctrl-shift while hitting enter. If you did this correctly, Excel will place braces {...} around the formula.

这篇关于如何在Excel中的两个日期之间返回多个值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！