在excel的日期之前找到星期一或星期四 [英] find the Monday or Wednsday before a date in excel

问题描述

我有一个在2010年的作业列表。每个都有到期日。作业必须在到期日前3个工作日提交外部处理。在作业可以发送到外部处理之前，必须进行审查。提交的审查是在星期一和星期三。

I have a list of assignments in excel 2010. Each has a due date. The assignment must be submitted for external processing 3 working days before the due date. Before the assignment can be sent to external processing, it must be reviewed. Submissions for review are on Mondays and Wednesday.

我想要一个功能，查看到期日期单元格的日期，并返回星期一或星期三的日期在日期之前的3个工作日之前更接近）;

I want a function that looks at the date in the due date cell and returns the date of the Monday or Wednesday (which ever is closer) before the date 3 workdays before that date;

X =（到期日前的3个工作日）

X = (3 workdays before the due date)

提交日期=（星期一或星期三之前）

submit date = (Monday or Wednesday before X)

我得到X， = WORKDAY.INTL（<到期日期单元格>， - 3）

现在我只需要代码

推荐答案

如果您的到期日是星期一，那么在上周三之前的3个工作日，那么你可以在星期三进行审查，还是需要在之前的星期一？如果是后者，那么您可以使用 WORKDAY 和 WORKDAY.INTL ，这样假设在A2中的到期日期

If your due date is a Monday then 3 workdays before that is the previous Wednesday then can you have the review on the Wednesday or does it need to be the Monday before that? If it's the latter then you can use WORKDAY and WORKDAY.INTL like this assuming due date in A2

= WORKDAY.INTL（工作日（A2，-3）， - 1，0101111）

如果是前者，那么只需将-3变为-2

If it's the former then just make the -3 into -2

通过这种方法，您可以使用WORKDAY返回2或3个工作日然后WORKDAY.INTL使用0101111表示周一和周三工作日，并在此基础上减去另一天

With this approach you use WORKDAY to go back 2 or 3 workdays and then WORKDAY.INTL uses "0101111" to indicate Mon and Wed working days and subtracts a further day on that basis

此方法可用于任何天数 - 后一个数字总是保持-1

This method can be used for any number of days - the latter number always remains -1

这篇关于在excel的日期之前找到星期一或星期四的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！